Converting SQL to LINQ to Entity

Question

I am trying to convert the following SQL, not written by myself, into a Linq to Entity query.

select 
    u.user_Id, 
    u.forename,
    u.surname,
    u.client_code,
    u.user_name, 
    u.password, 
    u.email, 
    u.gender, 
    u.Report_Date, 
    u.EmailDate,
    count(ut.test_Id) as testcount, 
    sum(cast(isnull(ut.completed,0) as int)) as Testcompleted, 
    u.job_function, 
    lu.lookupvalue
from 
users u inner join user_Relationship ur
    on u.user_Id= ur.child_Id       
left join user_tests ut
    on ut.user_id=u.user_id 
inner join lookup lu on u.first_languageId = lu.lookupid
where ur.parent_Id = @Parent_Id 
group by 
    u.user_Id, u.forename,u.surname,u.client_code,u.user_name, u.password, 
    u.email, u.gender, u.first_languageId, u.Report_Date,u.EmailDate,  
    u.job_function, lu.lookupvalue

So far, I have been able to do this:

from u in db.Users

join ur in db.User_Relationship on u.User_ID equals ur.Child_ID

join ut in db.User_Tests on u.User_ID equals ut.User_ID into ps
from ut in ps.DefaultIfEmpty()

join lu in db.Lookups on u.First_LanguageID equals lu.LookupID

where ur.Parent_ID == 45875

select new UserViewModel
{
    User_ID = u.User_ID,
    Forename = u.Forename,
    Surname = u.Surname,
    Client_Code = u.Client_Code,
    User_Name = u.User_Name,
    Password = u.Password,
    Email = u.Email,
    Gender = u.Gender,
    Report_Date = u.Report_date,
    Email_Date = u.EmailDate,
    //Insert Test_Count and Test_Completed
    Job_Function = u.Job_Function,
    Lookup_Value = lu.LookupValue
});

How do I replicate the Group and Count() function of SQL?

If it's taking you forever to do. So Why? – marko Sep 21 '12 at 12:33 — marko, Sep 21 '12 at 12:33

score 0 · Answer 1 · answered Sep 21 '12 at 12:58

Well tweak around this solution coz currently i dont have .Net environment for testing this solution, but you'll sure get how grouping is done in linq.

from u in db.Users

join ur in db.User_Relationship on u.User_ID equals ur.Child_ID

join ut in db.User_Tests on u.User_ID equals ut.User_ID into ps
from ut in ps.DefaultIfEmpty()

join lu in db.Lookups on u.First_LanguageID equals lu.LookupID

where ur.Parent_ID == 45875 group new{u,lu} by new {u.User_ID,u.Forename,
                          u.Surname,u.Client_Code,
                          u.User_Name,u.Password,
                          u.Email,u.Gender,u.Report_date,
                          u.EmailDate,u.Job_Function,
                          lu.LookupValue} into g
let Test_Count = ps.Count(x=>x.test_Id)
let Test_Completed = ps.Sum(x=>x.completed)
select new UserViewModel
                      {
                          User_ID = g.Key.User_ID,
                          Forename = g.Key.Forename,
                          Surname = g.Key.Surname,
                          Client_Code = g.Key.Client_Code,
                          User_Name = g.Key.User_Name,
                          Password = g.Key.Password,
                          Email = g.Key.Email,
                          Gender = g.Key.Gender,
                          Report_Date = g.Key.Report_date,
                          Email_Date = g.Key.EmailDate,
                          Test_Count = Test_Count,
                          Test_Completed = Test_Completed,
                          Job_Function = u.Job_Function,
                          Lookup_Value = lu.LookupValue
                      });

This is really helpful, thank you! I have a much better understanding of how grouping works. I'm still having problems with Test_Count and Test_Completed. I didn't mention that they are booleans and I am trying to return the total number of them as true. — user1688784, Sep 21 '12 at 13:33
i didn't get you about returning true ?? you mean if `count > 0` is that what you want to return ?? — FosterZ, Sep 21 '12 at 13:51
Sorry, it's a bit unclear without the tables. Without the grouping, the resultant return would be multiple UserViewModel entries that have the same information, but different Test_ID. I'm trying to get a count of the number of tests that have been completed. My understanding of the SQL query was that the boolean for Completed could be counted, thus giving a total number of completed tests. For Test_Count, this would be the number of tests that are linked the User_ID. Hope that clears things up. — user1688784, Sep 21 '12 at 14:13

Converting SQL to LINQ to Entity

1 Answers1