convert SQL to LINQ

Question

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!

Kailash Polai · Answer

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 Shukla · Answer

Please Follow this link. Hope it may help you. http://tutorial.techaltum.com/LINQ.html#article2

Natalija · Answer

Khan Abrar Ahmed Thank you very much. I will think about other solution.

Khan Abrar Ahmed · Answer

hi is it possible to send the table create schema

Natalija · Answer

with row w.Dump() I get the same error: Invalid 'where' condition....... - for C# Statement(s) too....

Khan Abrar Ahmed · Answer

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();

Natalija · Answer

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 Ahmed · Answer

Hi in LinqPad what language are you using??? can you select c# Statement and execute.

Natalija · Answer

Khan Abrar Ahmed Sorry but now I get error Invalid 'where' condition. An entity member is invoking an invalid property or method

Khan Abrar Ahmed · Answer

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;

Natalija · Answer

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'.

Natalija · Answer

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 Ahmed · Answer

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 Mishra · Answer

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.

Natalija · Answer

Khan Abrar Ahmed I have Boolean? type:

Natalija · Answer

Sorry I didn't write - I use LINQPad for execute

Khan Abrar Ahmed · Answer

HI check your property is this bolean or int

Abrar Ahmad Ansari · Answer

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); }

Natalija · Answer

sabyasachi mishra Thanks. With your code I get error message A query body must end with a select clause or a group clause

Natalija · Answer

Khan Abrar Ahmed Thanks. With your code I get error message: Operator '&&' cannot be applied to operands of type 'bool' and 'bool?'

Sabyasachi Mishra · Answer

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 Ahmed · Answer

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;