convert SQL to LINQ

Natalija

11y2.3kVisual Basic .NET

Hi, All.

I have SQL-request:

select FullName, CreatedBy, CreatedOn, DomainName, ModifiedBy from SystemUser

where SystemUserId In (

SELECT SystemUserId

FROM [UserSettings]

where [TimeZoneBias] != -60

and [TimeZoneCode] != 110)

and IsDisabled = 0

I need to convert this request into LINQ.

I wrote this (this is my first LINQ-request):

from s in SystemUserSet

where s.SystemUserId.Contains

(from sy in UserSettingsSet

where sy.TimeZoneBias != -60 && sy.TimeZoneCode != 110

select sy.SystemUserId)

&& s.IsDisabled = 0

select s

And I get error:

'System.Guid?' does not contain a definition for 'Contains' and the best extension method overload 'System.Linq.Queryable.Contains(System.Linq.IQueryable, TSource)' has some invalid arguments

Instance argument: cannot convert from 'System.Guid?' to 'System.Linq.IQueryable>'

Please, could you help with convert?

Thanks!

22 Replies

Know the answer? Post it — somebody with the same question will find it here.

Kailash Polai11y ago
Please try the following code

CSharpEntities db = new CSharpEntities();
var result = (from p in db.SystemUsers
join q in db.UserSettings on p.SystemUserId equals q.SystemUserId
where q.TimeZoneBias != -60 && q.TimeZoneCode != 110 && p.IsDisabled == false
select new
{
FullName = p.FullName,
CreatedBy = p.CreatedBy,
CreatedOn = p.CreatedOn,
DomainName = p.DomainName,
ModifiedBy = p.ModifiedBy
}).ToList();
Ankit Shukla11y ago
Please Follow this link. Hope it may help you.
http://tutorial.techaltum.com/LINQ.html#article2
Natalija11y ago
Khan Abrar Ahmed
Thank you very much.
I will think about other solution.
Khan Abrar Ahmed11y ago
hi is it possible to send the table create schema
Natalija11y ago
with row
w.Dump()
I get the same error: Invalid 'where' condition.......
- for C# Statement(s) too....
Khan Abrar Ahmed11y ago
Hi try user Dump() methode to print the output.

var obj = from o in SystemUser
join m in UserSettings on o.SystemUserId equals m.SystemUserId
where m.TimeZoneBias != -60 && Convert.ToInt32(m.TimeZoneCode) != Convert.ToInt32(110)
&& o.IsDisabled ==false
select o;
obj.Dump();
Natalija11y ago
I use C# Expression

Yes, with C# Statement(s) - query successful, but I don't see result of request.

And with
Console.Write(w);
or
w.Dump();
(for C# Statement(s))
I get the same error - Invalid 'where' condition....
Khan Abrar Ahmed11y ago
Hi in LinqPad what language are you using??? can you select c# Statement and execute.
Natalija11y ago
Khan Abrar Ahmed

Sorry, but now I get error

Invalid 'where' condition. An entity member is invoking an invalid property or method
Khan Abrar Ahmed11y ago
Hi try the below

var obj = from o in SystemUser
join m in UserSettings on o.SystemUserId equals m.SystemUserId
where m.TimeZoneBias != -60 && Convert.ToInt32(m.TimeZoneCode) != Convert.ToInt32(110)
&& o.IsDisabled ==false
select o;
Natalija11y ago
Khan Abrar Ahmed
Thanks.
It's seems works.
But now I get such error message:
Condition for attribute 'usersettings.timezonecode': expected argument(s) of type 'System.Int16' but received 'System.Int32'.
Natalija11y ago
sabyasachi mishra
The same error message.
But I changed your code, a little:

var qry1= from sy in UserSettingsSet
where sy.TimeZoneBias != -60 && sy.TimeZoneCode != 110 select sy;

var qry2= from s in SystemUserSet
where s.SystemUserId == qry1 && s.IsDisabled = 0 select s;

Now I get another error message:
Operator '==' cannot be applied to operands of type 'System.Guid?' and 'System.Linq.IQueryable'
Khan Abrar Ahmed11y ago
Hi Please try this

var obj = from o in SystemUser
join m in UserSettings on o.SystemUserId equals m.SystemUserId
where m.TimeZoneBias != -60 && m.TimeZoneCode != 110
&& o.IsDisabled ==false
select o;
Sabyasachi Mishra11y ago
var qry1= from sy in UserSettingsSet where sy.TimeZoneBias != -60 && sy.TimeZoneCode != 110 select sy var qry2= from s in SystemUserSet where s.SystemUserId == qry1.SystemUserId && s.IsDisabled = 0

Try this

----------------------------------------
Hope that helps.
Natalija11y ago
Khan Abrar Ahmed
I have Boolean? type:
Natalija11y ago
Sorry, I didn't write - I use LINQPad for execute
Khan Abrar Ahmed11y ago
HI check your property is this bolean or int
Abrar Ahmad Ansari11y ago
List SystemUserId = (from sy in UserSettingsSet
where sy.TimeZoneBias != -60 && sy.TimeZoneCode != 110
select sy.SystemUserId).ToList();
if(SystemUserId .Count()>0)
{
var result = (from s in SystemUserSet
where SystemUserId.Contains(s.SystemUserId)
&& s.IsDisabled == 0
select s);

}
Natalija11y ago
sabyasachi mishra
Thanks.
With your code I get error message
A query body must end with a select clause or a group clause
Natalija11y ago
Khan Abrar Ahmed
Thanks.
With your code I get error message:
Operator '&&' cannot be applied to operands of type 'bool' and 'bool?'
Sabyasachi Mishra11y ago
Better you can break this to 2 Linq query like this
var qry1=
from sy in UserSettingsSet
where sy.TimeZoneBias != -60 && sy.TimeZoneCode != 110
select sy.SystemUserId

var qry2=
from s in SystemUserSet
where s.SystemUserId=qry1 && s.IsDisabled = 0
----------------------------------------
Hope that helps.
Khan Abrar Ahmed11y ago
Hi try the below code

var obj = from o in SystemUser
join m in UserSettings on o.SystemUserId equals m.SystemUserId
where m.TimeZoneBias != -60 && m.TimeZoneCode != 110
&& o.IsDisabled =0
select o;