none
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
    Moderator
  • 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:

     

    <%#Eval("id")%>

     

    Using your approach I'm not sure. "id" is now - at least at object level - "behind", so I would have to access it using "A.id". 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...

     

    Regards

     

    Saturday, November 22, 2008 9:10 AM