Combine SELECT and SUM RRS feed

  • Question


    Hi group,


    probably a simple problem: I would like to combine the results of a SELECT from table A with a single SUM value on table B in LINQ. I know this can be done by creating a new anonymous result type, consisting of the SELECT elements and the SUM. The problem: The SELECT returns "tons" of colums and I don't want to write down every column name. How can I achieve this?


    Tuesday, November 18, 2008 11:38 AM

All replies

  • When you use LINQ you don't have to project out columns, you can project entire objects.


    var query = from a in db.TableA

                     select new { A = a, Sum = a.Bs.Sum(b => yyy) };




    Tuesday, November 18, 2008 9:26 PM
  • Hmm. Thanks for the hint, Warren. Although I doub, that this will work. I have to apply the results to a FormView as Datasource. The server side code (ASPX) accesses the elements of A e.g. this way:




    Using your approach I'm not sure. "id" is now - at least at object level - "behind", so I would have to access it using "". I haven't tested it, but I fear to see problems in this scenario.


    Basically I'm looking for a LINQ equivalent for this working T-SQL:


    select *, (select SUM(integer_column) from B where A.primary_key = B.foreignkey_to_A) from A where primary_key=any_number


    B has a foreign key to A, which is equivalent to A's primary key. B may have multiple entries; A just one, so the result is always 0 or 1 row. This row shall contain all columns of a, just as they have been selected "plain" and the SUM.


    Tuesday, November 18, 2008 10:22 PM
  • Hope this can solve your problem:


    From q In dc.Customers Select q.CustomerName, TotalSales = (From x In dc.Sales Where x.CustomerID = q.Customer Select x.Qty).Sum


    Where table A is Customer Table and Table B is Sales Info of Customer.





    Saturday, November 22, 2008 3:43 AM

    Hi Andy,


    thanks for your patience. It's going to get fun. Smile I think, your statement isn't correct, because - adapted to my scene - I think it should be made as:


    From q In dc.Customers Select new {q.CustomerName, TotalSales = (From x In dc.Sales Where x.CustomerID = q.Customer Select x.Qty).Sum}


    Than the statement returns correct results, ....b u t.... the statement _itself_ is not my only problem here. My problem: I would like to return ALL elements of q (~100), and I don't like to write them down element by element, because this seems rediculous, even if the table changes freqently.


    This can be done by stating:


    From q In dc.Customers Select new {q, TotalSales = (From x In dc.Sales Where x.CustomerID = q.Customer Select x.Qty).Sum}


    But the anonymous type returned is NOT the same as what the plain SQL statement above returns (Note: all Elements of q are now accessible via "q.columname" instead of "columname" before, so I would have to change a remarkable number of aspx pages (<%#Eval("columname")%>), just because I have changed the database engine in the code behind.


    Meanwhile I believe I cannot exchange SQL by LINQ for this problem, unfortunately...




    Saturday, November 22, 2008 9:10 AM