471,313 Members | 1,982 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,313 software developers and data experts.

Linq and the Let command

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

Sep 23 '08 #1
3 1911
Ilyas wrote:
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#)
------------------------------------------------------------------------
Sep 23 '08 #2
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();

?

--
Paul Musso
Sep 23 '08 #3
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;

Sep 23 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by clintonG | last post: by
1 post views Thread by news.microsoft.com | last post: by
2 posts views Thread by news.microsoft.com | last post: by
24 posts views Thread by luowan | last post: by
reply views Thread by Lloyd Sheen | last post: by
reply views Thread by =?Utf-8?B?SHlwZXJjb2Rlcg==?= | last post: by
4 posts views Thread by =?Utf-8?B?RXJpYyBGYWxza2Vu?= | last post: by
2 posts views Thread by =?Utf-8?B?UGFvbG8=?= | last post: by
13 posts views Thread by Dan Tallent | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.