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

Sorry, but SE is not a code writing service. (We are here to help you writing your code, not to write the code for you.) — jarlh
Welcome to Stack Overflow! Please never just dump SQL and ask for conversion. At least show a class model so navigation properties and the multiplicity of associations are visible. Also, tell what type of LINQ you're targeting (to entities?), and show your own first efforts. They clarify more to us than you might think. — Gert Arnold

Stefan Steiger Stefan Steiger · Accepted Answer · 2015-09-03T09:54:20

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
;

Convert SQL Server query to Linq query

1 Answers