Linq query and count question

[Solarion]

I have been stuck on this for a bit now. I have a Customer table and a Vehicle table. Some of the customers have 0 vehicles. Some of them have several.

I need to return all Customers that have 4 or more vehicles

Sounds simple, in LINQ I am struggling. This is the idea.

public List<Customers> GetCustomers()
{
      var result = (from customers in dbContext.customers
                          join vehicles in dbContext.vehicles
                          on customers.customerID equals vehicles.CustomerID
                          where customers.Count >= 4
                          select new Customers
                          {
                              Name = customers.Name,
                              CellNo = customers.CellNo,

                          }).ToList();
            return result;
}

 

[Hamster]

vehicles.Count? (Unless that returns the count of all vehicles).
My c# is rusty

 

[Ho3n3r]

public List<Customers> GetCustomers()
{
      var result = (from customers in dbContext.customers
                          join vehicles in dbContext.vehicles
                          group by requiredField1, requiredField2 into grp // add required fields here, adding table aliases
                          where grp.Count() >= 4
                          on customers.customerID equals vehicles.CustomerID
                          where customers.Count >= 4
                          select new Customers
                          {
                              Name = customers.Name,
                              CellNo = customers.CellNo,

                          }).ToList();
            return result;
}

 

[Hamster]

This is why I prefer regular SQL

select c.customerid, c.name, c.cellno, count(*) vehiclecount
from customers c
inner join vehicles v on v.customerid = c.customerid
group by c.cutomerid, c.name, c.cellno
having count(*) >= 4;

 

[Spacerat]

This is why I prefer regular SQL

select c.customerid, c.name, c.cellno, count(*) vehiclecount
from customers c
inner join vehicles v on v.customerid = c.customerid
group by c.cutomerid, c.name, c.cellno
having count(*) >= 4;

^^^ This

 

[Solarion]

This is why I prefer regular SQL

To be honest I might go back to regular Sql for now. It's faster for me I'm used to it.

select c.customerid, c.name, c.cellno, count(*) vehiclecount
from customers c
inner join vehicles v on v.customerid = c.customerid
group by c.cutomerid, c.name, c.cellno
having count(*) >= 4;

Thanks Ho3n3r

Edit: For the record the new forum is a lil confusing to quote people!

 

[[)roi(]]

This is why I prefer regular SQL

select c.customerid, c.name, c.cellno, count(*) vehiclecount
from customers c
inner join vehicles v on v.customerid = c.customerid
group by c.cutomerid, c.name, c.cellno
having count(*) >= 4;

The issue is broader than it not just being a good enough SQL-like thing.
Microsoft took common functional programming precepts like Functor, Monad, Monoid, etc... and rebadged it with their SQL-ese terms: Select, SelectMany, Aggregate, ...

Aside from it being an imperfect SQL; it does even approach a good rendition of the FP algebras on which it was based i.e imperfect on both ends.

I've always wondered why they decided the C# community couldn't handle learning the algebra behind this.

 

[Solarion]

^

All I know is, some of the more complex aggregate queries, I simply could not do in EF/LINQ. I could simply dump the sql query into EF sure, but that kind of defeated the purpose for me.

The project is already finished now. Sure SQL doesn't look great but I would still be scratching my head with LINQ.

 

[Hamster]

Sure SQL doesn't look great but I would still be scratching my head with LINQ.

Some would say it is LINQ that doesn't look great

 

[[)roi(]]

Some would say it is LINQ that doesn't look great

Yip. this IMO is the route that Linq should taken : https://github.com/louthy/language-ext

 

[GoB]

Starting with vehicles could be simple enough.

      var result = (from vehicle in dbContext.vehicles
                          group vehicle by vehicle.Customer into grp
                          where grp.Count() >= 4
                          select grp.Key); // Key is a Customer

I also found that the Linq to Sql becomes too complex fast and started using SQL for the read-only aggregate queries.

      var result = db.Database.SqlQuery<TypedResult>(sql, sqlParameters)