Unable to translate this SQL query into LINQ

Question

Hello everybody,

I've read lots and lots of web pages, all the MSDN, but I can't succeed in translating correctly this SQL Query:

Code Block

SELECT kl.naam, sum(f.enetto)
FROM kl
INNER JOIN faktuur f ON f.klnr = kl.klnr
WHERE kl.FirmaCode = 'K'

GROUP BY kl.naam
ORDER BY 2 DESC

I've tried this:

Code Block

CustList = from c in hiA.Customers
           where c.FirmCode == "K"
           select new CustKey()
           {

      CustName = c.Name,

                InvSum = c.Invoices.Sum(inv => inv.NetIncome)

 };

One problem is that this LINQ query displays the customers who have no invoices, whereas the SQL query does not.

So I changed it this way:

Code Block

CustList = from c in hiA.Customers
           where c.FirmCode == "K" && c.Invoices.Count() > 0
           select new CustKey()
           {

      CustName = c.Name,

                InvSum = c.Invoices.Sum(inv => inv.NetIncome)

      };

But there is still a problem.

In the Customers table, there are customers who are twice, with some different unique IDs. So the LINQ Query gives this kind of results:

Foo    1234€
Foo    12€
Bar    345€

I'd like to group the result by the customer's Name, and to do the sum of all invoice incomes for each customer, so I would get this:

Foo    1246€
Bar    345€

I've fighted with the group by and join clauses, but no way for me to make it work...

Could anyone give me some help?

Thanks in advance

Mike

Answer 1

i think you can try this one:
http://msdn2.microsoft.com/en-us/vcsharp/aa336754.aspx#simple1

grouping select into temporary variable, and then select again with count
haven't tested, but something like this (as long as i understand your crypto-names and you have):

Code Block

CustList = from c in hiA.Customers

group by c.Name into tmp
where c.FirmCode == "K"

select new {

CustName = tmp.Name,

InvSum = tmp.Invoices.Sum(p=>p.NetIncome)

}

Answer 2

Try the following query:

var custList = from i in hiA.Invoices
                     where i.Customer.FirmCode == "K"

 group i by i.Customer.Name into oc
 select new { CustomerName = oc.Key, InvoicesSum = oc.Sum(inv => inv.NetIncome) };
 

Answer 3

That works nice!

Thanks you very much for your answer.

I didn't think about "inversing" the query and putting the table Invoices in the first from clause...

I'll had to remember that in the future

Thanks again

Answer 4

Mmm, that query works, but I would like to operate it in 2 times.

                   

Code Block

class CustKey
{
    public string CustName;
    public double? InvSum;
}

IQueryable<CustKey> CustList;

//Select all the results of that query

var query = from i in hiA.Invoices
            where i.Customers.FirmCode == "K" && i.Customers.Name.Contains(filter)
            group i by i.Customers.Name into oc
            orderby oc.Key
            select new
            {
                CustName = oc.Key,
                InvList = oc.Select(inv => inv)
            };

//If the user has choosen to add a where clause based on the Year, refilter the results
if (CASortingPeriod_.SelectedIndex == 1)
{
    query = from r in query
            select new
            {
                CustName = r.CustName,
                InvList = r.InvList.Where(inv => inv.Year == Convert.ToInt32(CASortingYear_.Text))
            };
}

//Now convert all the results into a IQueryAble<CustKey> structure

CustList = from r in query
           select new CustKey()
           {
               CustName = r.CustName,
               InvSum = r.InvList.Sum(inv => inv.NetIncome)
           };
}                

CustList = CustList.Distinct();

//The sorting method might change

if (SortingType_.SelectedIndex == 0)
    CustList = CustList.OrderBy(c => c.InvSum);
else
    CustList = CustList.OrderByDescending(c => c.InvSum);
}

foreach (var c in CustList)

FilterList.Items.Add(c.CustName + " (" + c.InvSum.ToString() + ")");

The problem is that the query throws an exception because of a time-out.

Why is it like that?

Should I use any method like AsEnumerable() or ToList() at any moment?

And a last question:

to avoid a compiler error complaining about the type of CustKey::InvSum, I had to give it the type double?

Is there any way to avoid that?

Thanks in advance

Mike