Linq: select from multiple tables into one pre-defined entity

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

For example:

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'd like to add a property to domain object A called it TotalQuantity and populate it with g.Sum( b => b.quantity ). I'd also like to turn 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 domain A and B doesn't contain any reference to each other. Their relationship is not used explicitly in the application, therefore, I chose not to model it.

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

This should do it (note I have not tested it so some tweaking may be in order):

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});

You'll have perform your projection in memory instead of the database. This way the LINQ to SQL provider won't attempt to convert it to an SQL query.

Here's an example:

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
                        });

The call to the IQueryable<T>.ToArray() method will force the LINQ to SQL provider to run the query against the database and return the results in an array. The final projection is then performed in memory, circumventing the limitations of the LINQ to SQL provider.

Related resources:

• LINQ and Deferred Execution
• The performance implications of IEnumerable vs. IQueryable