Convert SQL Server query to Linq query

Question

select c.Name, d.First_Name, COUNT(c.Name) as qty 
from order_product_s a 
    inner join Order_s b on a.Order_Id = b.Id
    inner join Product_s c on a.Product_Id = c.Id 
    inner join Customer_s d on b.Customer_Id = d.Id 
where b.Customer_Id = 4869 
group by c.Name, d.First_Name

Answer 1

Something like this:

int __UserId = 4869;

var results = 
( 
    from t in 
    (
        from a in Repo.order_product_s 
        from b in Repo.Order_s 
             .Where(bb=> bb.id == a.Order_Id)
        from c in Repo.Product_s 
             .Where(cc => cc.Id == a.Product_Id)
        from d in Repo.Customer_s  
             .Where(dd => dd.Id == b.Customer_Id)

        where b.Customer_Id == __UserId 

        select new
        {
             Name = c.Name
            ,First_Name = d.First_Name
        }

    )
    group t by new { t.Name , t.First_Name } into g
    select new 
    { 
         Name  = g.Key.Name 
        ,First_Name=g.Key.First_Name
        ,qty = g.Count( x => x.Name != null)
    }

).ToList();

or more compact:

var results = 
( 
    from a in Repo.order_product_s 
    from b in Repo.Order_s 
         .Where(bb=> bb.id == a.Order_Id)
         // .DefaultIfEmpty() // <== makes join left join         
    from c in Repo.Product_s 
         .Where(cc => cc.Id == a.Product_Id)
         // .DefaultIfEmpty() // <== makes join left join         
    from d in Repo.Customer_s  
         .Where(dd => dd.Id == b.Customer_Id)
         // .DefaultIfEmpty() // <== makes join left join         

    where b.Customer_Id == __UserId 

    select new
    {
         Name = c.Name
        ,First_Name = d.First_Name
    }
    into t group t by new { t.Name , t.First_Name } into g
    select new 
    { 
         Name  = g.Key.Name 
        ,First_Name=g.Key.First_Name
        ,qty = g.Count( x => x.Name != null)
         // Or like this
        // ,qty = g.Select(x => x.Name).Where(x => x != null).Count()
        // and if you ever need count(distinct fieldname)
        //,qty = g.Select(x => x.GroupName).Where(x => x != null).Distinct().Count()
    }

)
// .OrderBy(t => t.Name).ThenBy(t => t.First_Name).ThenBy(t => t.qty) // Order in SQL 
.ToList()
// .OrderBy(t => t.Name).ThenBy(t => t.First_Name).ThenBy(t => t.qty) // Order in .NET
;