0

Hello i am totally new to Linq. I need to convert the following query to Linq and having a pretty hard time. Almost 3 hrs spent still unable to figure out. Most of the functions/methods in Sql like Distinct, Not in etc are missing in Linq. Even if they are available i am unable to figure out how to use them. Are there any alternative Methods/Functions in Linq with different names that i should be using or they don't even exist in Linq and i need to use a different approach ? I would be really helpful if someone could help me in converting the following query to Linq.

SQL Query

Select count(distinct(UserID))  from dbo.DeansStudents 
             inner join dbo.UserBarSession on DeansStudents.UserBarSessionID = UserBarSession.ID
             inner join dbo.Users on users.ID = UserBarSession.UserID                 
             where UserBarSessionID not in (


             Select b.ID from dbo.DeansStudents,dbo.Users
             left join dbo.Answers on answers.Student=users.ID
             left join dbo.UserBarSession b on Answers.Student = b.UserID
             where AnswerDate between b.StartDate and b.EndDate 
             and AnswerDate between 7/10/2011 and 3/12/2018
             and UserBarSessionID = b.ID and DeanID= 12296 group by Answers.Student,users.FirstName,users.LastName,b.ID) and DeanID =12296

Query converted so far to LINQ

From my past couple of days into LINQ i managed to converted the first part of the Sql Query to LINQ. I am unable to continue with the second part. From "Select b.id........ "

 var query = from deansStudent in dbo.DeansStudents
             join userBarSession in dbo.UserBarSession
             on deansStudent.UserBarSessionId equals userBarSession.Id
             join users in dbo.Users
             on userBarSession.UserId equals users.Id 

               //Need continuation from here
Vasalath
  • 83
  • 1
  • 8
  • To answer this we need your data model. Your SQL itself seems to be buggy and questionable itself to deduct the model. – Cetin Basoz Jul 16 '18 at 20:46
  • Yes i understand that. Its 18 yrs old application and don't even know who wrote this sql queries back than. Anyways thanks for your reply. Will figure out some way out to rewrite the sql first before jumping to linq. – Vasalath Jul 16 '18 at 20:52
  • OK. Hint: With a well designed database you very rarely need to use "join"keyword. Instead you use "dot notation" along the "navigational properties". Do yourself a favor and download LinqPad. If your db is well designed it would show the navigational properties. – Cetin Basoz Jul 16 '18 at 20:55
  • @CetinBasoz thats what i am using in my current application which i am remodelling now. Mostly lambda or o data notations. As i am new to it thot of writing linq and converting it back to lambda as i did for earlier queries. But this one seems to be something difficult – Vasalath Jul 16 '18 at 21:00

1 Answers1

0

Most of the functions/methods in Sql like Distinct, Not in etc are missing in Linq

Distinct() is part of Linq, and NOT IN can be accomplished with the Except() Linq extension method.

With this answer, you should be able to finish your query.

Lews Therin
  • 3,707
  • 2
  • 27
  • 53
  • This sounds to be a comment, not an answer. – Cetin Basoz Jul 16 '18 at 20:47
  • @CetinBasoz His question was how to convert the SQL query to a Linq query, given that Linq lacked some equivalents to SQL operators. I linked documentation to the Linq methods that will allow him to do what he's trying to accomplish. How does this not answer his question? I'm not going to write the code for him. – Lews Therin Jul 16 '18 at 20:50
  • I am not the one who downvotes your "comment". I think it is still a comment and no where near an answer to the OP question. – Cetin Basoz Jul 16 '18 at 20:52
  • @LewsTherin thanks for the link. I do see methods there. Will go through it. – Vasalath Jul 16 '18 at 20:55