Linq and the Let command

[Ilyas]

Hi all

In the northwind database, on the orders table, How would I get back
the the number of items that exist with the following condition:

ShipVia=1
ShipVia=2
ShipVia=3

I want to do this in one database hit

I have tried:

Northwindcontext c=new Northwindcontext();
var (from x in c.Orders
let total1=c.Orders.Count(y=>y.ShipVia=1)
let total2=c.Orders.Count(y=>y.ShipVia=2)
let total3=c.Orders.Count(y=>y.ShipVia=3)
).SingleOrDefault();

but this doesnt work - I know why because I get an entry for every row
in the orders table, but how do use the Let command so that I get just
one row back?

Many thanks

 

[Frans Bouma [C# MVP]]

Hi all

In the northwind database, on the orders table, How would I get back
the the number of items that exist with the following condition:

ShipVia=1
ShipVia=2
ShipVia=3

I want to do this in one database hit

I have tried:

Northwindcontext c=new Northwindcontext();
var (from x in c.Orders
let total1=c.Orders.Count(y=>y.ShipVia=1)
let total2=c.Orders.Count(y=>y.ShipVia=2)
let total3=c.Orders.Count(y=>y.ShipVia=3)
).SingleOrDefault();

but this doesnt work - I know why because I get an entry for every row
in the orders table, but how do use the Let command so that I get just
one row back?

What you seem to want is a query with 3 scalar queries in the
projection (3 count queries on orders) and the FROM clause is really not
important, as the data returned is retrieved in the scalars.

This is inefficient.

What you should use is something like:
SELECT COUNT(*) as Amount, ShipVia
FROM Orders
WHERE ShipVia IN (1, 2, 3)
GROUP BY ShipVia

which gives you 3 rows, and the query is efficient.

This is in linq something like:

var q = from o in ctx.Orders
where new int[] {1, 2, 3}.Contains(o.ShipVia)
group o by o.ShipVia into g
select new { Amount = g.Count(), ShipVia = g.Key};


FB
--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Paul Musso]

Ilyas a formulé ce mardi :

Hi all

In the northwind database, on the orders table, How would I get back
the the number of items that exist with the following condition:

ShipVia=1
ShipVia=2
ShipVia=3

I want to do this in one database hit

I have tried:

Northwindcontext c=new Northwindcontext();
var (from x in c.Orders
let total1=c.Orders.Count(y=>y.ShipVia=1)
let total2=c.Orders.Count(y=>y.ShipVia=2)
let total3=c.Orders.Count(y=>y.ShipVia=3)
).SingleOrDefault();

but this doesnt work - I know why because I get an entry for every row
in the orders table, but how do use the Let command so that I get just
one row back?

Many thanks

Hi Ilyas,

Tryed to do something like that :

(from x in c.Orders
select new {
Total1=c.Orders.Count(y=>yShipVia==1)
Total2=c.Orders.Count(y=>yShipVia==2)
Total3=c.Orders.Count(y=>yShipVia==3)
}).FirstOrDefault();

?

 

[Peter Morris]

I don't know the syntax, but I think a better approach would be to group

(in sql)
select count(*), ShipVia from orders where ShipVia in (1,2,3) group by
ShipVia;