Please help me write LINQ statement for following SQL query

Question

My Db named MyDB has 5 tables: MSize, MModel, Met, MResult, SResult. They are connected as follows:

MSize has a common field MSizeId with MModel.

MModel links with Met with MModelId.

Met can be linked with MResult on basis of MId.

Similarly SResult can be linked with MResult on SResultId.

My aim is to get average accuracy of all the items(description field in Msize table) with Acc(decimal data type) >=70 and <=130 grouped by description.

Here is my SQL query:

use MyDB;
SELECT a.[Description],AVG(CASE WHEN d.[Acc] >= 70 AND d.[Acc] <= 130 THEN d.[Acc] END)
  FROM MSize a 
  INNER JOIN MModel b   ON a.MSizeId = b.MSizeId
  INNER JOIN Met c      ON b.MModelId = c.MModelId
  INNER JOIN MResult d  ON c.MId = d.MId
  INNER JOIN SResult e  ON d.SResultId = e.SResultId
  GROUP BY a.Description

This query gives me the correct result on SQL server.

I have been struggling to write a LINQ query for the same. The problem comes with the SQL CASE statement. I don't want to specify the false result of the CASE, meaning, if d.acc doesn't fall in the range specified in SQL query, discard it.

Assuming all Model classes and fields have the same name as these DBtables and columns. What can be the LINQ query for the given SQL statement?

You can fill up the code here in curly braces:

using (var db = new MyDBContext()){ }

here MyDBContext refers to Partial Class Data Model template generated by LINQ

If I'm reading this correctly you're grouping by a `bool`. `m_size_group.Key` is bool. So filter it with `where m_size_group.Key` before the final select. — Dialecticus, Feb 13 '19 at 22:00
Also, trying to read your mind here, but `mSize` should be maybe `m_size_group.Count()`. — Dialecticus, Feb 13 '19 at 22:03
Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) could help you? — NetMage, Feb 13 '19 at 22:40
Could you edit your question, give us the relevant classes and their relations; leave out all the joins that are not important for the query, write in words your requirement that made you invent this SQL-statement: "give me all ... from ... that ... where ..." — Harald Coppoolse, Feb 14 '19 at 08:43

score 0 · Answer 1 · answered Feb 18 '19 at 13:46

You didn't bother to write the classes, and I'm not going to do that for you.

Apparently you have a sequence of MSizes, where every Msize has zero or more MModels. Every MModel has zero or more Mets, and every Met has zero or more MResults, and every MResult has an Acc.

You also forgot to write in words your requirements, now I had to extract it from your SQL query

It seemt that you want the Description of every MSize with the average value of all the Accs that it has, that have a value between 70 and 130.

If you use entity framework, you can use the virtual ICollection which makes live fairly easy. I'll do it in two steps, because below I do the same with a GroupJoin without using the ICollection. The 2nd part is the same for both methods.

First I'll fetch the Description of every MSize, together with all its deeper Acc that are in the MResults of the Mets of the MModels of this MSize:

var descriptionsWithTheirAccs = dbContext.MSizes.Select(msize => new
{
    Description = msize.Description,

    // SelectMany the inner collections until you see the Accs
    Accs = mSize.Mmodels.SelectMany(

        // collection selector:
        model => model.Mets,

        // result selector: flatten MResults in the Mets
        (model, mets) => mets
            .SelectMany(met => met.MResults,

            // result Selector: from every mResult take the Acc
            (met, mResults) => mResults
                  .Select(mResult => mResult.Acc)));

Now that we have the Description of every MSize with all Accs that it has deep inside it, we can throw away all Accs that we don't want and Average the remaining ones:

var result= descriptionsWithTheirAccs.Select(descriptionWithItsAccs => new
{
    Description = descriptionWithItsAccs.Description,

    Average = descriptionWithItsAccs.Accs
        .Where(acc => 70 <= acc && acc <= 130)
        // and the average from all remaining Accs
        .Avg(),
});

If you don't have access to the ICollections, you'll have to do the Groupjoin yourself, which looks pretty horrible if you have so many tables:

var descriptionsWithTheirAccs = dbContext.MSizes.GroupJoin(dbContext.MModels,
    msize => msize.MSizeId,
    mmodel => mmodel.MSizeId,
    (msize, mmodels) => new
    {
        Description = msize.Description,

        Accs = mmodels.GroupJoin(dbContext.Mets,
            mmodel => mModel.MModelId,
            met => met.MModelId,
            (mmodel, metsofThisModel) => metsOfThisModel
                .GroupJoin(dbContext.MResults,
                met => met.MetId
                mresult => mresult.MetId,

                // result selector
                (met, mresults) => mResult.Select(mresult => mresult.Acc))),
      });

Now that you have the DescriptionsWithTheirAccs, you can use the Select above to calculation the Averages.

Please help me write LINQ statement for following SQL query

1 Answers1