SELECT s.SortCode,
       ISNULL(t.Name, ISNULL(s.SortName, '')) AS [Name]
FROM   dbo.Sorts s
       LEFT OUTER JOIN dbo.Translations t ON  s.SortCode = t.Code AND t.CultureCode = 'ru-ru'
	   JOIN dbo.Products p ON s.SortCode = p.SortCode
WHERE p.DisplayInListFlag = 'Y' 

var res = from s in db.Sorts
          join t in db.Translations
                on new { s.SortCode, CultureCode = "ru-ru" }
            equals new { SortCode = t.Code, t.CultureCode } into t_join
          from t in t_join.DefaultIfEmpty()
          join p in db.Products on s.SortCode equals p.SortCode
          where p.DisplayInListFlag == 'Y'
          select new
          {
              s.SortCode,
              Name = (t.Name ?? (s.SortName ?? string.Empty))
          };

SELECT [t0].[SortCode],
       COALESCE([t1].[Name], COALESCE([t0].[SortName], @p2)) AS [Name]
FROM   [dbo].[Sorts] AS [t0]
       LEFT OUTER JOIN [dbo].[Translations] AS [t1]
            ON  ([t0].[SortCode] = [t1].[code])
            AND (@p0 = [t1].[CultureCode])
       INNER JOIN [dbo].[Products] AS [t2]
            ON  [t0].[SortCode] = [t2].[SortCode]
WHERE  UNICODE([t2].[DisplayInListFlag]) = @p1

from s in db.Sorts
join t in db.Translations
      on new { s.SortCode, CultureCode = "ru-ru" }
  equals new { SortCode = t.Code, t.CultureCode } into t_join
from t in t_join.DefaultIfEmpty()
where
  	(from t0 in db.Products
	where
	  t0.DisplayInListFlag == "Y"
	group t0 by new {
	  t0.SortCode
	} into g
	select new {
	  g.Key.SortCode
	}).Contains(new { s.SortCode })
select new {
  s.SortCode,
  NAME = (t.Name ?? (s.SortName ?? string.Empty))
}

SELECT [t0].[SortCode],
       COALESCE([t1].[Name], COALESCE([t0].[SortName], @p2)) AS [NAME]
FROM   [dbo].[Sorts] AS [t0]
       LEFT OUTER JOIN [dbo].[Translations] AS [t1]
            ON  ([t0].[SortCode] = [t1].[code])
            AND (@p0 = [t1].[CultureCode])
WHERE  EXISTS(
           SELECT NULL AS [EMPTY]
           FROM   (
                      SELECT [t2].[SortCode]
                      FROM   [dbo].[Products] AS [t2]
                      WHERE  UNICODE([t2].[DisplayInListFlag]) = @p1
                      GROUP BY
                             [t2].[SortCode]
                  ) AS [t3]
           WHERE  [t3].[SortCode] = [t0].[SortCode]
       )

SELECT s.SortCode,
       ISNULL(t.Name, ISNULL(s.SortName, N'')) AS NAME
FROM   dbo.Sorts s
       LEFT OUTER JOIN dbo.Translations t
            ON  s.SortCode = t.Code
            AND t.CultureCode = 'ru-ru'
WHERE  (
           s.SortCode IN (SELECT SortCode
                          FROM   dbo.Products
                          WHERE  (DisplayInListFlag = 'Y')
                          GROUP BY
                                 SortCode)
       )

CultureCode = 'ru-ru'

DisplayInListFlag = 'Y'

using (ClassesDataContext db = new ClassesDataContext())
{
    //Get SQL                    
    sql.Text = string.Empty;
    context.Log = new ActionTextWriter(s => sql.Text += s);

    // Готовим запрос на Count(*) и выполняем
    var query = string.IsNullOrEmpty(tbRoleName.Text) ?
        context.aspnet_Roles :
        context.aspnet_Roles.Where(d => d.RoleName.StartsWith(tbRoleName.Text));
    int totalRecords = query.Count();

    // Готовим запрос, но не выполняем
    var result = query.OrderBy(d => d.RoleName)
        .Skip(gridPager.PageIndex * gridPager.PageSize)
        .Take(gridPager.PageSize);

    grid.DataSource = null;
    int currentRecords = 0;

    if (totalRecords > 0)
    {
        var list = result.ToList();
        grid.DataSource = list;
        currentRecords = list.Count;

    }

    gridPager.Render(totalRecords, currentRecords);
}