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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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.