none
Multiple columns from two different tables and a having count RRS feed

  • Question

  • Hello,

    I am wondering if you can help me figuring how to move this SQL to Linq. I am using EF6:

    select Table1.Column1, Table1.Column2, TableB.ColumnA, Table1.Column3, Count(*) as occur 
    from Table1 inner join TableB  on Table1.Table1ID = TableB.Table1ID 
    where TableB.Columm4 not like '%discount%' and TableB.ColumnB = 'Y' 
    group by Table1.Column1, Table1.Column2, TableB.ColumnA, Table1.Column3
    having count(*) > 1
    order by occur desc
     

    Please notice that I am grouping columns from two different tables. Also, the object resulting has to contain the count(*).

    Any help is much appreciated!

    thanks


    paburgos

    Wednesday, December 7, 2016 1:15 PM

Answers

  • Hi paburgos,

    Please refer to the following LINQ statement:

    var query = from p in db.Table1s
                                join a in db.TableBs on p.Table1ID equals a.Table1ID
                                where !a.Columm4.Contains("discount") && a.ColumnB == "Y"
                                group new { p, a } by new { p.Column1, p.Column2, p.Column3, a.ColumnA } into grp
                                where grp.Count() > 1
                                select new
                                {
                                    Column1 = grp.Key.Column1,
                                    Column2 = grp.Key.Column2,
                                    Column3 = grp.Key.Column3,
                                    ColumnA = grp.Key.ColumnA,
                                    occur = grp.Count()
                                };

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by paburgos Thursday, December 8, 2016 6:56 AM
    Thursday, December 8, 2016 1:23 AM
    Moderator