none
linq to sql using group by RRS feed

  • Question

  • I have a C# 2008 application that uses the following linq to sql statement to connect to a sql ser ver 2008 r2 database.  

       var lcount = (from iw in erptData.Ibooks
                        join ip in eData.IPackages on iw.Package_ID equals ip.Package_ID
                        join impt in eData.Import_Error on iw.Workbook_ID equals impt.Workbook_ID
                        join tt in eData.Transaction_Types on impt.Trans_Type_ID equals tt.Trans_Type_ID
                        where ip.Tracking_Number == packageId
                        group impt by impt.Contract_Number_Err into g
                        select new { Contract_Number_Err = g.Key, Frequency = g.Count() }).FirstOrDefault();

    I would like the linq to sql statement to translate to the following sql statement:


    select distinct(t.data_errors),
    COUNT(t.Import_Error_Trans_id), ip.Tracking_Number,t.Contract_Number_Err
    from dbo.Import_Error t
    inner join  dbo.Ibooks iw on t.Workbook_ID = iw.Workbook_ID
    inner join dbo.Import_Package ip on ip.Package_ID = iw.Package_ID
    --inner join dbo.Plans p on p.Plans_id = t.Plans_ID
    where ip.Tracking_Number = 'XXXXXX'
    group by ip.Tracking_Number,t.Contract_Number_Err,t.data_errors

    I do not know how to get the ip.Tracking_Number,t.Contract_Number_Err values in the group by statement
    and in the annoymous select statement.

    I would like the counts distinct by tracking number,Followed by contract number.

    Thus can you tell me how to write the linq statements to accomplish this goal?


    Wednesday, January 16, 2013 11:24 PM

Answers

All replies