LINQ: select test

[ck1]

Hi at all - i want to move my first step into linq

DATA:
- Class Order
- id (from database)
- id_user (from database)
- data (from database)
- total (from database)
- total2 (property of partial-class in O/R designer that i add)

QUERY (select all order of user 3 and foreach order take the total2):

var q = from order in myContext.Order
where order.id_user == 3
select order;

foreach(var order in q)
{
order.total2 = from totals in myContext.total2
where totals.id_order = q.id
select totals.total2;
}

How i can do this script without use "foreach" ?

ck1;

 

[Marc Gravell]

Are you expecting multiple orders for user 3? In which case, via an
ORM "foreach" is the simplest route. If you want to avoid iteration
you'd have to do it as TSQL in the database, not via an object model.

Marc

 

[ck1]

Marc Gravell ha scritto:

Are you expecting multiple orders for user 3? In which case, via an
ORM "foreach" is the simplest route. If you want to avoid iteration
you'd have to do it as TSQL in the database, not via an object model.

Yes - user 'id=3' can have multiple order.
in TSQL it's a simply sub-query, so LINQ haven't a subquery ?


ck1;

 

[Marc Gravell]

in TSQL it's a simply sub-query

For *query*, then yes it does - however, you have presented an UPDATE
scenario. LINQ is a query language (hence the "Q") - data updates are
actually done separately, typically by updating the individual objects
which the ORM can then persist back into the database. As such,
"foreach" is fine.

As an aside, you could probably reduce the query to one hit instead of
"n+1" using a projection to do the aggregate - i.e.

var qry = ... select new {Order = order, Total = [todo]};
foreach(var row in qry) {
row.Order.Total = row.Total;
}
// commit

- but it is hard to give an exact syntax example without a "known"
database to talk about (Northwind, Adventureworks, etc).

Marc

 

[Marc Gravell]

For comparison, here's a similar query that gets (along with each northwind
customer), the total freight on all their orders:

var qry = from c in db.Customers
join o in db.Orders on c.CustomerID equals
o.CustomerID into sr
from x in sr.DefaultIfEmpty() // left outer
join
let row = new {Cust = c, Order = x}
group row by row.Cust into grp
select new { Customer = grp.Key, Total =
grp.Sum(x=>x.Order.Freight)};

I have deliberately used a separate join here (rather than using navigation
properties) to be comparable to the scenario where there *is* no navigation
property.

Marc