convert sql query to entity framework - linq query

Question

0.00/5 (No votes)

See more:

I am using following query.
But i got error in Convert.ToInt16().
In database field powerplatecapacity data type is nvarchar.
I want to check betwwen condition.
My code is like following.

SQL

from _power in dataContext.Powers
                         join _powerFuelTypes in dataContext.PowerFuelTypes on _power.ID equals _powerFuelTypes.PowerID into list1
                         from l1 in list1.DefaultIfEmpty()
where (Convert.ToInt16(_power.PowerPlateCapacity) >= 0 && Convert.ToInt16_power.PowerPlateCapacity) <= 100)

Posted 22-Oct-12 21:22pm

pk patel

Add a Solution

Comments

Zoltán Zörgő 23-Oct-12 3:41am

"powerplatecapacity data type is nvarcha", but why?

paamar 29-Mar-13 4:35am

Hi all,
Could you please help in converting the below SQL query into LINQ am using C#

need to convert below query in LINq--Drop table #FinalData-- Drop table #sm_DATA

--Drop table #t1Begin Create table #FinalData ( SeriesID1 int,

MetaTagsID1 int , Value1 varchar (50), dtStart datetime, dtEnd datetime,

grp int )Select * INTO #sm_DATA From (

Select Mv.SeriesId, mv.MetaTagsId,mv.Value, mv.PeriodStart, mv.PeriodEnd from

seriesgroup sg inner join (

select SeriesGroupId,GroupNumber, count (*) cnt1 from SeriesGroupFilter

where Exclude = 0

group by SeriesGroupId , GroupNumber ) GN on gn.SeriesGroupId =sg.SeriesGroupId

inner join SeriesGroupFilter sgf on sgf.GroupNumber = gn.GroupNumber

inner join (

Select GroupNumber, count (*) cnt2 From (Select Distinct sgf.GroupNumber, mv.MetaTagsId, mv.Value from SeriesGroupFilter sgf

inner join MetaTagValues mv on ( mv.MetaTagsId = sgf.MetaTagsId and MV.Value = sgf.Value )

group by mv.MetaTagsId, mv.Value,sgf.GroupNumber ) mvcnt group by GroupNumber

) recmv on ( recmv.GroupNumber = sgf.GroupNumber and recmv.cnt2 = gn.cnt1)

inner join MetaTagValues MV on ( MV.MetaTagsId = sgf.MetaTagsId and MV.Value = sgf.Value )

inner join Series s on s.SeriesId = mv.SeriesIdwhere sg.SeriesGroupId =55 ) a

/* Select * from #sm_DATA*//* Drop table #sm_DATA*/

Declare @MetaTagsId int, @SeriesID int, @cnt numeric Set @cnt = 1;

Declare SM cursor for SELECT smd.SeriesId, smd.MetaTagsId from #sm_DATA smd

group by smd.SeriesId, smd.MetaTagsId, smd.value having COUNT (*) > 1

OPEN SM FETCH NEXT FROM sm INTO @SeriesID, @MetaTagsId

WHILE @@FETCH_STATUS = 0 BEGIN

Select * into #t1 from #sm_DATA where SeriesId = @SeriesID and MetaTagsId = @MetaTagsId

Declare @SeriesID1 int, @MetaTagsID1 int , @Value1 varchar (50),

@dtStart datetime, @dtEnd datetime Declare SM2 cursor for select * from #t1

Open SM2

FETCH NEXT FROM sm2 INTO @SeriesID1, @MetaTagsID1, @Value1, @dtStart, @dtEnd

WHILE @@FETCH_STATUS = 0 BEGIN /* set @cnt = 1;*/

Insert into #FinalData values (@SeriesID1, @MetaTagsID1, @Value1, @dtStart, @dtEnd, @cnt)

Insert into #FinalData Select *,@cnt from #sm_DATA where SeriesId = @SeriesID and MetaTagsId not in (@MetaTagsId)

set @cnt = @cnt + 1;

FETCH NEXT FROM SM2 into @SeriesID1, @MetaTagsID1, @Value1, @dtStart, @dtEnd

END CLOSE SM2;DEALLOCATE SM2;FETCH NEXT FROM sm INTO @SeriesID, @MetaTagsId

END CLOSE SM;DEALLOCATE SM;Select * from #FinalData

Select seriesID1, MAX (dtStart), MIN (dtend) From #FinalData group by seriesID1,grp

End

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**Zoltán Zörgő** · Accepted Answer · 2012-10-22T21:51:00

Solution 1

This is working properly in LinqPad:

C#

var vs = from v in "10 12 100 33".Split() select v;
vs.Dump();

var x = from v in vs where Convert.ToInt16(v)>90 select v;
x.Dump();

But I am a little bit confused about you query. You select into list1, than you select from it. Why do you think that _power will be in scope of that query? I am pretty sure you are complicating the query. It is not best-practice, but try to think of this query as sql query first: how would you formulate it?

Posted 22-Oct-12 21:51pm

Zoltán Zörgő

Updated 22-Oct-12 21:54pm

v3

Comments

pk patel 23-Oct-12 8:18am

actually i didn't get you?
let me explain.
i want entity framework query for sql query like following.

"select * from powers where CONVERT(int,PowerPlateCapacity) >= 100 and CONVERT(int,PowerPlateCapacity) <= 101

Zoltán Zörgő 23-Oct-12 12:38pm

from p in dataContext.Powers where (Convert.ToInt16(p.PowerPlateCapacity) >= 100 && Convert.ToInt16(p.PowerPlateCapacity) <= 101) select p

pk patel 24-Oct-12 0:20am

i had tried the same query.
but i got exceptation in convert.ToInt16.

"LINQ to Entities does not recognize the method 'Int16 ToInt16(System.String)' method, and this method cannot be translated into a store expression."

Zoltán Zörgő 24-Oct-12 4:10am

I have to admit, I was trying linq to objects, because I don't have your database. Wait...!

Zoltán Zörgő 24-Oct-12 4:58am

You are missing something. I have made a test app with the above expression both in LINQ to SQL and Entity Framework. And it is working!

pk patel 24-Oct-12 5:25am

Can u send me that test app???

Zoltán Zörgő 24-Oct-12 5:32am

How?

pk patel 24-Oct-12 6:03am

can you mail me???

Zoltán Zörgő 24-Oct-12 8:38am

See mail

Zoltán Zörgő 25-Oct-12 12:04pm

got my mail yesterday?

pk patel 7-Nov-12 23:21pm

yes i got your mail.
but same code i have used.
but its ok. i have found alternative for that.

Zoltán Zörgő 8-Nov-12 4:37am

So, it's solved than?

pk patel 8-Nov-12 22:56pm

ya.. it solved.

convert sql query to entity framework - linq query

1 solution

Solution 1

Add your solution here

Preview 0

convert sql query to entity framework - linq query

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us