CODEWITHSUNDEEP
c#linq
Rolando
Rolando

Reputation: 762

LINQ: bad conversion from LINQ to sql query

I am getting some problem with the conversion of linq query to sql query. The source of the problem is when i am using IBPUNO field what is a string type in the edmx file and nchar in sql server)

var query = (from c in ContextM3.MPLINE where c.IBSUNO == supplier.M3Code.Trim() &&       orderNumbers.Contains(c.IBPUNO)

The conversion of the "where" clause from linq query to sql servr is something like:

...WHERE ([Extent1].[IBSUNO] = (LTRIM(RTRIM(@p__linq__0)))) AND ([Extent1].[IBPUNO] IN (N''177828'',N''7115912'))',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'8100033   '

If I execute this sql query in the ssms I got no rows but if i change the sql query to

WHERE ([Extent1].[IBSUNO] = (LTRIM(RTRIM(@p__linq__0)))) AND ([Extent1].[IBPUNO] IN (177828,7115912))',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'8100033   '

then woks fine and it retrieve the rows what i want (the change what i did is translate the values N''177828'' TO only 177828 so my question how i can say to Net do this change??

[Extent1].[IBPUNO] IN (N''177828'',N''7115912'))

TO [Extent1].[IBPUNO] IN (177828,7115912))

Upvotes: 1

Views: 993

Answers (1)

Risky Martin
Risky Martin

Reputation: 2521

You can store integers instead of strings in orderNumbers and convert c.IBPUNO to an int using Convert.ToInt32():

var orderNumbers = new [] { 177828, 7115912 };
var query = (from c in ContextM3.MPLINE 
             where c.IBSUNO == supplier.M3Code.Trim() &&       
                   orderNumbers.Contains(Convert.ToInt32(c.IBPUNO))
             // select etc...

If possible you should have IBPUNO be an int in the first place.

Upvotes: 2

Related Questions