I want to execute a Linq to Sql statement that captures (filtered) counts and averages in the data set. My working principle, but in a query should be able to perform the database Two queries.
Interestingly, when I use the group by clause, I can get a query.
For example:
< /p>
select count(*), avg(duration) from events
My linq looks like this:
var x = from e in db.events
select e;
x = from i in x
where i.NAME == "foo"
select i;
return new {
count = x.Count(),
avgDuration = x.Average(e => e.Duration)
};
Use this code, I get two queries:
SELECT AVG([t0].[DURATION]) AS [value] FROM [dbo].[EVENTS] AS [t0]
and
SELECT COUNT(*) AS [value] FROM [dbo].[EVENTS] AS [t0]
There are other Is there a way?
The best I can get is nested subqueries:
var x = from e in db.events
group e by 1 into grp
select new {
count = grp.Count(), < br /> avgDuration = grp.Average(x => x.Duration) }
According to LINQPad, this will output SQL:
DECLARE @p0 Int = 1
SELECT COUNT(*) AS [count], AVG([t1].[Amount]) AS [avgDuration]
FROM (
SELECT @p0 AS [value ], [t0].[Duration]
FROM Events AS [t0]
) AS [t1]
GROUP BY [t1].[value]
< /p>
I want to execute a Linq to Sql statement that captures the counts and averages in the (filtered) data set. My working principle, but it should be possible to query the database twice in one query. < p>
Interestingly, when I use the group by clause, I can get a query.
For example:
select count(*), avg(duration) from events
My linq looks like this:
var x = from e in db. events
select e;
x = from i in x
where i.NAME == "foo"
select i;
return new {< br /> count = x.Count(), avgDuration = x.Average(e => e.Duration)
};
Using this code, I get two queries:
SELECT AVG([t0].[DURATION]) AS [value] FROM [dbo].[EVENTS] AS [t0]
and
< pre>SELECT COUNT(*) AS [value] FROM [dbo].[EVENTS] AS [t0]
Is there another way?
The best I can get is nested subqueries:
var x = from e in db.events
group e by 1 into grp
select new {
count = grp.Count(),
avgDuration = grp.Average(x => x.Duration) }
According to LINQPad, this will output SQL:
DECLARE @p0 Int = 1
SELECT COUNT (*) AS [count], AVG([t1].[Amount]) AS [avgDuration]
FROM (
SELECT @p0 AS [value], [t0].[Duration]
FROM Events AS [t0]
) AS [t1]
GROUP BY [t1].[value]