Create A linq query with multiple where statements and left join

Question

I am trying to write a LINQ query that will render that following SQL. I am close but it still isn't correct.

Desired SQL

SELECT  

      TableDisplayColumns.Id
    , TableDisplayColumns.ViewName
    , TableDisplayColumns.ColumnName
    , TableDisplayColumns.ControllerName
    , TableDisplayColumns.TableDisplayName
    , TableDisplayColumns.IsVisibleByDefault

FROM dbo.TableDisplayColumns
LEFT JOIN dbo.UserTableDisplayColumns ON UserTableDisplayColumns.TableDisplayColumnId = TableDisplayColumns.Id

WHERE 
             (
                (TableDisplayColumns.IsVisibleByDefault = 1  AND UserTableDisplayColumns.IsNotVisible IS NULL)
                OR UserTableDisplayColumns.IsNotVisible = 0
             )
    AND (TableDisplayColumns.ControllerName = 'Contacts' AND TableDisplayColumns.ViewName = 'Index')

Current SQL

DECLARE @p0 NVarChar(1000) = 'Index'
DECLARE @p1 NVarChar(1000) = 'Contacts'
-- EndRegion
SELECT [t0].[Id], [t0].[ColumnName], [t0].[IsVisibleByDefault], [t0].[TableDisplayName], [t0].[ViewName]
FROM [TableDisplayColumns] AS [t0]
LEFT OUTER JOIN ([UserTableDisplayColumns] AS [t1]
    LEFT OUTER JOIN [TableDisplayColumns] AS [t2] ON [t2].[Id] = [t1].[TableDisplayColumnId]) ON [t0].[Id] = [t1].[TableDisplayColumnId]
WHERE ([t0].[ViewName] = @p0) AND ([t0].[ControllerName] = @p1) AND ([t0].[IsVisibleByDefault] = 1) AND ([t2].[IsVisibleByDefault] = 1)

Entities

public class UserTableDisplayColumn
{
    public int Id { get; set; }

    public int TableDisplayColumnId { get; set; }

    public bool IsNotVisible { get; set; }

    public virtual TableDisplayColumn TableDisplayColumn { get; set; }
}

public class TableDisplayColumn
{

    public int Id { get; set; }

    [StringLength(50), Required]
    public string ViewName { get; set; }

    [StringLength(50), Required]
    public string ColumnName { get; set; }

    [StringLength(50), Required]
    public string ControllerName { get; set; }

    [StringLength(50), Required]
    public string TableDisplayName { get; set; }

    [Required]
    public bool IsVisibleByDefault { get; set; }
}

Finally the current query:

    var query =  
            (
                from column in Context.TableDisplayColumns
                    .Where(x=>x.IsVisibleByDefault)
                    .Where(x=>x.ControllerName == "Contacts")
                    .Where(x=>x.ViewName == "Index")
                join userColumn in Context.UserTableDisplayColumns on column.Id equals userColumn.TableDisplayColumnId into userColumnColumns

                from userColumnColumn in userColumnColumns.DefaultIfEmpty()

                select new TableDisplayColumn()
                {
                    Id = column.Id,
                    ColumnName = column.ColumnName,
                    ControllerName = column.ColumnName,
                    IsVisibleByDefault = column.IsVisibleByDefault,
                    TableDisplayName = column.TableDisplayName,
                    ViewName = column.ViewName
                }
             ).ToList();

What I am trying to accomplish get the default column if there isn't anything in the UsersColumns Table. If there is a record in the UsersColumns then I want to only return if IsNotVisible = false.

LINQ isn't a SQL replacement. You are using an *ORM*, which means you don't have tables, you have *entities* with relations and properties. Use proper relations and navigation properties instead of trying to hard-code joins. It's the ORM's job to generate the joins based on the relations between *entities* — Panagiotis Kanavos, Oct 05 '18 at 14:01
Which ORM are you using? Entity Framework? LINQ by itself doesn't retrieve data from databases. As for examples, every ORM tutorials shows how to map tables to entities, create and confifure relations — Panagiotis Kanavos, Oct 05 '18 at 14:12
[This tutorial](http://www.entityframeworktutorial.net/code-first/configure-one-to-many-relationship-in-code-first.aspx) shows how to configure a one-to-many relation in EF 6 — Panagiotis Kanavos, Oct 05 '18 at 14:14
@PanagiotisKanavos Actually, I disagree. LINQ is exactly a SQL replacement. Entity Framework is an ORM. Using LINQ to SQL you can just substitute for SQL. — NetMage, Oct 05 '18 at 18:31
Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) would help you translate? BTW, you appear to have an error in the `ControllerName` assignment in the `select`. — NetMage, Oct 05 '18 at 18:34
Also, if you are using EF, which version / type are you using? You appear to have a navigation property going in the opposite direction of your join for your query - is there some reason you don't have a reverse property? — NetMage, Oct 05 '18 at 18:42
@NetMage I am using EF6. I didnt know that i have a good reason for that. an answer with the correct query would be helpful. — 3xGuy, Oct 05 '18 at 18:58

score 0 · Answer 1 · answered Oct 05 '18 at 14:11

You can use the let keyword to bring in collections in a left join-ish way.

var query =
  from x in Context.TableDisplayColumns
  where x.ControllerName == "Contacts"
  where x.ViewName == "Index"
  let ys = Context.UserTableDisplayColumns.Where(y => y.TableDisplayColumnId == x.Id)
  where (x.IsVisibleByDefault && !ys.Any()) || ys.Any(y => y.IsNotVisible == 0)
  select x;

Create A linq query with multiple where statements and left join

1 Answers1