CODEWITHSUNDEEP
c#linqentity-frameworkentity-framework-6
tickwave
tickwave

Reputation: 3443

Is it possible to assign single linq query result to more than one variable?

I can do this in TSQL

SELECT
    @TotalDays = COUNT(Days),
    @TotalHours = SUM(Hours)
FROM
    Schedule
WHERE
    GroupID = 1

How to achieve this in linq in single query, my current code;

var totalDays = 0;
var totalHours = 0;

totalDays = _schedule.Count(c => c.GroupID == 1);
totalHours = _schedule.Where(w => w.GroupID == 1).Sum(s => s.Hours);

This is not effective because it call 2 separate queries in the database

Upvotes: 1

Views: 176

Answers (3)

user2896170
user2896170

Reputation:

You can use the next code 

 //one request to data base
    var lstResult=_schedule.Where(w => w.GroupID == 1).ToArray();
    //one loop in array for Count method
    totalDays = lstResult.Count();
    //One loop in array for Sum method
    totalHours = lstResult.Sum(s => s.Hours);

Upvotes: 0

Enigmativity
Enigmativity

Reputation: 117027

The problem, sometimes, trying to make a single LINQ query is that it actually gets translated into multiple database calls. Sometimes it is better to pull all of your raw data into memory in a single database call and then perform the calculations.

This will ensure only one database call:

var data = _schedule.Where(w => w.GroupID == 1).Select(w => w.Hours).ToArray();

var totalDays = data.Count();
var totalHours = data.Sum();

The key to making this work is the .ToArray() which forces the evaluation of the database query. If there are a lot of items this call can become inefficient, but in lot of cases it is still very fast.

Upvotes: 1

Christos
Christos

Reputation: 53958

You could try something like this:

var result = _schedule.Where(s => s.GroupID == 1)
                      .GroupBy(x => x.GroupID)
                      .Select(gr => new
                      {
                          TotalDays = gr.Count(),
                          TotalHours = gr.Sum(s=>s.Hours);
                      });

Initially, you filter your data based on the GroupID. You pick those with GroupID equals to 1. Then you GroupBy them by their ID. This mihgt seams a bit silly, but this way you create a group of your data. So then you count just count the item in the group and calculate the sum you want. Last but not least after having made the GroupBy, you select an anonymous type with two properties, one for the TotalDays and one for the TotalHours.

Then you can consume the above result as below:

var totalDays = 0;
var totalHours = 0;
var first = result.FirstOrDefault();
if(first!=null)
{
    totalDays = first.TotalDays,
    totalHours = first.TotalHours
};

Upvotes: 5

Related Questions