Geek asks and answers

Linq: select from multiple tables into one predefined object

I have two tables A and B. A domain object retrieves most of its data from A and some aggregation from B.

For instance:

Table A ( id, name );
Table B ( id_A, quantity );

class A {
     public int id { set; get; }
     public string name { set; get; }
}
class B {
     public int id_A { set; get; }
     public int quantity { set; get; }
}

var result = 
from a in A join b in B on a.id equals b.id_A
group b by b.id_A into g
select new {
     Name = a.name,
     Total = g.Sum( b => b.quantity )
};

Instead of creating an anonymous type, I would like to add a property to an object of domain A called its TotalQuantity, and populate it with g.Sum (b => b.quantity). I would also like to turn the result into IEnumerable instead of var.

My first bet was

class A {
     public int id { set; get; }
     public string name { set; get; }
     public int TotalQuantity { set; get; }
}

IEnumerable<A> result = 
from a in A join b in B on a.id equals b.id_A
group b by b.id_A into g
select new A {
     name = a.name,
     TotalQuantity = g.Sum( b => b.quantity )
};

This operation is not supported by the runtime:

System.NotSupportedException: Explicit construction of entity type 'Data.A' in query is not allowed.

Note that domains A and B do not contain links to each other. Their relationship is not used explicitly in the application, so I decided not to model it.

How can I neatly populate list A without cycling the data stored in instances of the anonymous class?

+3
2

. , LINQ to SQL SQL-.

:

IEnumerable<A> result = (from a in A join b in B on a.id equals b.id_A
                         group b by b.id_A into g
                         select new
                         {
                             Name = a.name,
                             Total = g.Sum(b => b.quantity)
                         })
                        .ToArray()
                        .Select(item => new A
                        {
                            Name = item.Name,
                            TotalQuantity = item.Total
                        });

IQueryable < T.Array() LINQ to SQL . , LINQ to SQL.

:

+2

( , , ):

IEnumerable <A> result = 
(from a in A join b in B on a.id equals b.id_A
group b by b.id_A into g
select new {
     Name = a.name,
     Total = g.Sum( b => b.quantity )
}).Select(obj => new A {Name = obj.Name, TotalQuantity = obj.Total});
+3

More articles:


All Articles