CODEWITHSUNDEEP
c#sql-serverlinqlinq-to-sqlquery-optimization
LaRae White
LaRae White

Reputation: 1272

How to leverage linq to sql better to optimize filtering and sorting data?

I have this code where I am trying to sort the top five most reported serotypes in a pathogen. Let's say there are 800+ serotypes that could possibly be reported. I start with an ObjectQuery that holds all the reports for that given pathogen. I now need to count the number of reports grouped by each serotype and pull back the top 5. This is how I have it written now:

Dictionary<string, int> unsorted = new Dictionary<string, int>();

//'serotypes' is an objectquery of 800+ serotypes
foreach (LookupSerotype serotype in serotypes)   
{
    var count = Reports.Count(r => r.serotypeId == serotype.serotypeId);
    unsorted.Add(serotype.serotypeName, count);
}

// convert to list in memory in order to sort the data
var sorted = unsorted.ToList();

sorted.Sort(delegate(KeyValuePair<string, int> first, KeyValuePair<string, int> next)
{
    return first.Value.CompareTo(next.Value);
});

// reverse because the largest were sorted to the bottom
sorted.Reverse();

Can I convert most (if not all) of this to more linq to sql in order to have these calculations done in query to the db? Or is there a quicker/better way entirely to pull this information?

Specifically the first foreach loop at the beginning takes the longest and I could use help with

Upvotes: 0

Views: 65

Answers (2)

Ivan Stoev
Ivan Stoev

Reputation: 205739

If serotypes and Reports are EF IQueryables, and assuming serotype.serotypeId is PK (unique), then you can use a single EF query based on group join like this:

var query = 
    (from st in serotypes
     join r in Reports on st.serotypeId equals r.serotypeId into stReports
     order by st.serotypeName descending
     select new { st.serotypeName, reportCount = stReports.Count() }
    ).Take(5);

var result = query
    .AsEnumerable() // switch to LINQ to Objects context
    .Select(e => new KeyValuePair<string, int>(e.serotypeName, e.reportCount))
    .ToList();

The only tricky part is the need to switch to LINQ to Objects context before the final projection, because EF does not support projecting (select) to class/struct w/o parameterless constructor.

Upvotes: 1

user5684647
user5684647

Reputation:

If you want to do this with LINQ, this should get you close:

var topFive = serotypes.GroupBy(s => s.serotypeName)
         .Select(s => new
         {
            SeroTypeName = s.serotypeName,
            Total = s.Count()
         }
         .OrderByDescending()
         .Take(5)
         .ToList();

Also, if you want to do some of the work on the server via a query, you can use this SQL:

Select Top 5 serotype.serotypeName, Count(*) as Total
  from serotype
group by serotype.serotypeName
order by Total Desc

Upvotes: 1

Related Questions