CODEWITHSUNDEEP
c#linq
Christian Sauer
Christian Sauer

Reputation: 10899

Linq How to compute row count?

I am exploring a very messy SQL-Database via LINQPAD and I would like to get a list of all tables where at least one column is of type string. Furthermore, I would like to compute the count of distinct values for each column mentioned above.

I have tried to jumble something together, but my LINQ is rusty and anyway I have used it mainly for LINQ to Objects...

foreach(var table in Mapping.GetTables())
{
(
from dm in table.RowType.DataMembers 
where dm.Type == typeof(string)
select new { dm.Name , dm.DbType , dm.Type , dm.MappedName, dm.IsPrimaryKey } 
)
}

Upvotes: 2

Views: 338

Answers (1)

dav_i
dav_i

Reputation: 28107

Well I think you were pretty much on the right track... the following seems to work (though is quite slow on the database I'm testing on):

from table in Mapping.GetTables()
from member in table.RowType.DataMembers
where member.Type == typeof(string)
let count = ExecuteQuery<int>(String.Format(
                "SELECT COUNT(DISTINCT {0}) FROM {1}",
                member.Name,
                table.TableName)).FirstOrDefault()
select new { table.TableName, member.Name, count }

Upvotes: 1

Related Questions