How to convert Sql query to EntityFramework?

Question

SQL

SET @SQL_A = 'SELECT dbo.Nav.L AS p1,p2,Count(p3) AS Count FROM dbo.Nav with(nolock)
WHERE ' + @Link + ' ' + @Filter + '
GROUP BY dbo.Nav.Link ,Nav.Vendor ORDER BY Nav.Vendor'

Answer 1

Something like this (written from memory without a studio):

var paramLink = …;
var paramFilter = …;
var result Nav.Where(nav => nav.Link == paramLink && nav.Filter == paramFilter)
    .GroupBy(nav => new {Link = nav.Link,Vendor = nav.Vendor} , nav => nav)
    .OrderBy(grNav => grNav.Key.Vendor).Select(grNav => new {Link = grNav.Key.Link, Vendor = grNav.Key.Vendor, Count = grNav.Count()});

Instead of the anonymous class new {Link = nav.Link,Vendor = nav.Vendor}, it might be better to use something like KeyValuePair or another class that compares object attributes to establish equality between objects.