Linq. Count.

[shapper]

Hello,

I have a table named Tags which is related to other two tables
ArticlesTags and FilesTags.

My linq query to select my tags would be something like:

var query = from t in db.Tags
select new
{
TagId = t.TagId,
TagText = t.TagText,
Active = t.FilesTags.Any Or t.ArticlesTags.Any
}

What active specifies if a Tag is associated to a File or
Article.

Now I need to create a new query that retrieves 2 values:

1. Number of existing tags in table Tags.
2. Number of tags which are related to FilesTags or ArticlesTags.

I need to retrieve these 2 values to display in 2 ASP.NET labels.

Could someone, please, help me with this query?

Thanks,
Miguel

 

[shapper]

Hello,

I have a table named Tags which is related to other two tables
ArticlesTags and FilesTags.

My linq query to select my tags would be something like:

var query = from t in db.Tags
select new
{
TagId = t.TagId,
TagText = t.TagText,
Active = t.FilesTags.Any Or t.ArticlesTags.Any
}

What active specifies if a Tag is associated to a File or
Article.

Now I need to create a new query that retrieves 2 values:

1. Number of existing tags in table Tags.
2. Number of tags which are related to FilesTags or ArticlesTags.

I need to retrieve these 2 values to display in 2 ASP.NET labels.

Could someone, please, help me with this query?

Thanks,
Miguel

Please, anyone?

Thank You,
Miguel

 

[Marc Gravell]

If I understand correctly, and using Northwind as a (hopefully similar
example), how about the below; note that it only does a single SQL
query (because we force the ToArray()), but to be honest, I wouldn't
shy away from 2 simpler selects - one for just the total Count(), and
one for the Count() with a Where()... anyway, see how this goes:

var data = ctx.Customers.GroupBy(
cust => cust.CustomerCustomerDemos.Any() ||
cust.Orders.Any())
.Select(grp => new { Linked = grp.Key, Count =
grp.Count() }).ToArray();

foreach (var row in data)
{
Console.WriteLine("{0}: {1}", row.Linked,
row.Count);
}
Console.WriteLine("Total: {0}",
data.Sum(row=>row.Count));

There might be some other options... I'll see if anything occurs...

Marc

 

[Marc Gravell]

I found a better answer; the tricky part was getting
deferred execution, as normally Count() executes immediately.
(of course, you could just do 2 simple queries? one Count(),
one Where().Count())

I've approached this instead by using a projection from the
primary table, and used FirstOrDefault, which means that
even if there are no rows, an object with 0 in both counts
is returned.

Aside: note that the .Count(predicate) [even with the Expression
form] does a very funky looking query, hence the simpler
..Where(predicate).Count() usage.

Anyway, the C# is:

var counts = ctx.Customers.Select(x =>
new
{
Total = ctx.Customers.Count(),
Linked = ctx.Customers.Where(
cust => cust.CustomerCustomerDemos.Any()
|| cust.Orders.Any()).Count()
}).FirstOrDefault();

With the faitly reasonable-looking generated SQL:

SELECT TOP (1) [t5].[value] AS [Total], [t5].[value2] AS [Linked]
FROM (
SELECT (
SELECT COUNT(*)
FROM [dbo].[Customers] AS [t1]
) AS [value], (
SELECT COUNT(*)
FROM [dbo].[Customers] AS [t2]
WHERE (EXISTS(
SELECT NULL AS [EMPTY]
FROM [dbo].[CustomerCustomerDemo] AS [t3]
WHERE [t3].[CustomerID] = [t2].[CustomerID]
)) OR (EXISTS(
SELECT NULL AS [EMPTY]
FROM [dbo].[Orders] AS [t4]
WHERE [t4].[CustomerID] = [t2].[CustomerID]
))
) AS [value2]
FROM [dbo].[Customers] AS [t0]
) AS [t5]

I have looked at the query plan/IO, and it isn't doing anything /too/ crazy.