C# Linq multiple queries in a single one

Question

I'm new in Linq and I'm trying to optimize some queries and I don't have any idea if it is possible for this queries:

var cSRez = (from l in MyTable
                 where l.Name == "dcc" && l.Service == "Main"
                 orderby l.Time descending
                 select l.Value).FirstOrDefault();
var cDRez = (from l in MyTable
                 where l.Name == "dcc" && l.Service == "DB"
                 orderby l.Time descending
                 select l.Value).FirstOrDefault();
var dSRez = (from l in MyTable
                 where l.Name == "ddc" && l.Service == "Main"
                 orderby l.Time descending
                 select (long?)l.Value).FirstOrDefault();
var dDRez = (from l in MyTable
                 where l.Name == "ddc" && l.Service == "DB"
                 orderby l.Time descending
                 select (long?)l.Value).FirstOrDefault();
var mSRez = (from l in MyTable
                  where l.Name == "mc" && l.Service == "Main"
                  orderby l.Time descending
                  select l.Value).FirstOrDefault();
var mDRez = (from l in MyTable
                  where l.Name == "mc" && l.Service == "DB"
                  orderby l.Time descending
                  select l.Value).FirstOrDefault();

to become a single one. I was thinking about row_number() over(partition by... (SQL) but I don't think this is the best idea for doing this.

It is possible to collapse this six separate queries into a single one?

@maccettura There would also need to use some type of group by because of the FirstOrDefault. — juharr
– juharr, Commented Sep 11, 2018 at 18:12
What exactly do you want to achieve by combining it into one query? you are selecting only 1 result each time, do you want to have all the results in one variable? — Ziv Weissman
– Ziv Weissman, Commented Sep 11, 2018 at 18:14
@juharr it wouldn't. But the query does the same thing over and over again so just calling a method is better. I should not have been lazy and taken the time to explain that. — Sach
– Sach, Commented Sep 11, 2018 at 18:25

juharr · Accepted Answer · 2018-09-11 18:27:54Z

4

You'd need to group on the Name and Service and then filter on the specific pairs you want then select the Name and Service and the Value from the First match. Note that any pairs that do not exist will not be represented in the results and you'd have to handle that when you pull the values out.

var results = (from l in MyTable
              group l by new {l.Name, l.Service} into grp
              where (grp.Key.Name == "dcc" && grp.Key.Service == "Main")
                  || (grp.Key.Name == "dcc" && grp.Key.Service == "DB")
                  || ....
              select new
              {
                  grp.Key,
                  Value = grp.OrderByDescending(x => x.Time).Select(x => x.Value).First()
              }).ToDictionary(x => x.Key, x => x.Value);

Then to pull out the results

results.TryGetValue(new { Name = "dcc", Service = "Main" }, out var cSRez);

edited Sep 11, 2018 at 18:27

answered Sep 11, 2018 at 18:20

juharr

32.4k4 gold badges60 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sergey Kalinichenko · Accepted Answer · 2018-09-11 18:33:06Z

I am not sure how EF would translate this query to SQL, but I would try this approach:

var rawData = MyTable
    .Where(l => (l.Name=="dcc" || l.Name=="ddc" || l.Name=="mc") && (l.Service=="Main" || l.Service=="Db"))
    .GroupBy(l => new { l.Name, l.Service })
    .Select(g => g.OrderByDescending(l => l.Time).First())
    .ToList();

This should yield up to six rows of interest to your program. Now you can retrieve each row by specifying the specific combination of Name and Service:

var cSRez = rawData.FirstOrDefault(l => l.Name == "dcc" && l.Service == "Main");
var cDRez = rawData.FirstOrDefault(l => l.Name == "dcc" && l.Service == "DB");
var dSRez = rawData.FirstOrDefault(l => l.Name == "ddc" && l.Service == "Main");
var dDRez = rawData.FirstOrDefault(l => l.Name == "ddc" && l.Service == "DB");
var mSRez = rawData.FirstOrDefault(l => l.Name == "mc" && l.Service == "Main");
var mDRez = rawData.FirstOrDefault(l => l.Name == "mc" && l.Service == "DB");

Note that the six queries on rawData are performed in memory on a list of fixed size of up to six items, so they are not costing you additional round-trips to RDBMS.

NetMage · Accepted Answer · 2018-09-11 19:25:11Z

I am not sure if this will execute faster as a single query or not, as the query is somewhat complex, so I think it may depend on server side query time versus query setup and data transmission time.

You can convert into a single query that gathers all the answers at once, and then break that answer up for each variable.

This first answer takes the result and converts into a double nested Dictionary for the values and then pulls the variables out of the Dictionary:

var ansd = (from l in MyTable
            where new[] { "dcc", "ddc", "mc" }.Contains(l.Name) && new[] { "Main", "DB" }.Contains(l.Service)
            group l by new { l.Name, l.Service } into ag
            select new {
                ag.Key.Name,
                ag.Key.Service,
                Value = ag.OrderByDescending(l => l.Time).First().Value
            })
            .GroupBy(nsv => nsv.Name)
            .ToDictionary(nsvg => nsvg.Key, nsvg => nsvg.ToDictionary(nsv => nsv.Service, arv => arv.Value));

long? cSRez = null, cDRez = null, dSRez = null, dDRez = null, mSRez = null, mDRez = null;
if (ansd.TryGetValue("dcc", out var td)) td.TryGetValue("Main", out cSRez);
if (ansd.TryGetValue("dcc", out td)) td.TryGetValue("DB", out cDRez);
if (ansd.TryGetValue("ddc", out td)) td.TryGetValue("Main", out dSRez);
if (ansd.TryGetValue("ddc", out td)) td.TryGetValue("DB", out dDRez);
if (ansd.TryGetValue("mc", out td)) td.TryGetValue("Main", out mSRez);
if (ansd.TryGetValue("mc", out td)) td.TryGetValue("DB", out mDRez);

Given that there are only six answers, creating Dictionarys for them may be overkill. Instead, you can just (sequentially) find the matching answers:

var ansl = (from l in MyTable
            where new[] { "dcc", "ddc", "mc" }.Contains(l.Name) && new[] { "Main", "DB" }.Contains(l.Service)
            group l by new { l.Name, l.Service } into ag
            select new {
                ag.Key.Name,
                ag.Key.Service,
                Value = ag.OrderByDescending(l => l.Time).First().Value
            })
            .ToList();

var cSRez = ansl.FirstOrDefault(ansv => ansv.Name == "dcc" && ansv.Service == "Main");
var cDRez = ansl.FirstOrDefault(ansv => ansv.Name == "dcc" && ansv.Service == "DB");
var dSRez = ansl.FirstOrDefault(ansv => ansv.Name == "ddc" && ansv.Service == "Main");
var dDRez = ansl.FirstOrDefault(ansv => ansv.Name == "ddc" && ansv.Service == "DB");
var mSRez = ansl.FirstOrDefault(ansv => ansv.Name == "mc" && ansv.Service == "Main");
var mDRez = ansl.FirstOrDefault(ansv => ansv.Name == "mc" && ansv.Service == "DB");

Sharif · Accepted Answer · 2018-09-11 18:29:52Z

0

Just put your query in a private method that takes an Exression and returns Value and call it for each variable (e.g. cDRez, cSRez etc) simply passing different expressions.

private Value GetValue(Expression<Func<MyTable, bool>> filter) {
    return MyTable.Where(filter).OrderByDescending(o => o.Time).Select(s => s.Value).FirstOrDefault();
}

Call it like with different filters for each variable:

var cSRez = GetValue(l => l.Name == "dcc" && l.Service == "Main");
var cDRez = GetValue(l => l.Name == "dcc" && l.Service == "DB");
var dSRez = GetValue(l => l.Name == "ddc" && l.Service == "Main");
var dDRez = GetValue(l => l.Name == "ddc" && l.Service == "DB");
var mSRez = GetValue(l => l.Name == "mc" && l.Service == "Main");
var mDRez = GetValue(l => l.Name == "mc" && l.Service == "DB");

answered Sep 11, 2018 at 18:29

Sharif

3734 silver badges12 bronze badges

1 Comment

juharr Over a year ago

That doesn't make it one query. That just dries out the existing code which I don't think is what the OP was asking for.

Collectives™ on Stack Overflow

C# Linq multiple queries in a single one

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related