How to remove where condition if the filter is empty in EF6

Question

How to remove where condition if the filter is empty in EF6

entity-framework-6

Question

My query is give below. If id =0 I dont want that in my where clause also if title is empty then I need to remove that from the where clause .Is there any way to do that. I 'm new in EF I tried a lot but still no luck.

public dynamic GetData(int id,string title){

    var entryPoint = (from ep in dbContext.tbl_EntryPoint
                     join e in dbContext.tbl_Entry on ep.EID equals e.EID
                     join t in dbContext.tbl_Title on e.TID equals t.TID
                     where e.OwnerID == id || t.title==title
                     select new {
                         UID = e.OwnerID,
                         TID = e.TID,
                         Title = t.Title,
                         EID = e.EID
                     }).Take(10);
}

Optimus

View more on Stack Overflow

Jonathan Gilbert · Accepted Answer · 1/19/2017 8:49:43 PM

There are several levels at which this can be done. You can embed it into the generated SQL by using a where clause like where ((e.OwnerID == id) || (id == 0)), or you can go entirely the other way and have four separate copies of the entire LINQ expression for different where clause variants. I personally recommend a mid-way approach: use separate branches of code to build different IQueryable values based on the filter, without repeating the common parts:

public dynamic GetData(int id, string title)
{
    var baseQuery =
        from ep in dbContext.tbl_EntryPoint
            join e in dbContext.tbl_Entry on ep.EID equals e.EID
            join t in dbContext.tbl_Title on e.TID equals t.TID
        select new { e, t };

    var filtered = baseQuery; // Implicitly type filtered to match the anonymous type in baseQuery

    if (id > 0)
    {
        if (!string.IsNullOrWhiteSpace(title))
            filtered = baseQuery.Where(ep => (ep.e.OwnerID == id) || (ep.t.title == title));
        else
            filtered = baseQuery.Where(ep => ep.e.OwnerID == id);
    }
    else
    {
        if (!string.IsNullOrWhiteSpace(title))
            filtered = baseQuery.Where(ep => ep.t.title == title);
        else
            filtered = baseQuery;
    }

    var entryPoint = filtered.Select(ep =>
        new
        {
            UID = ep.e.OwnerID,
            TID = ep.e.TID,
            Title = ep.t.Title,
            EID = e.EID
        }).Take(10);

    ...
}

Entity Framework is smart enough to know that, in the anonymous type constructed in baseQuery, ep.e refers to the tbl_Entry joined table, and ep.t refers to the tbl_Title joined table. Here's a sample of the generated SQL from the above code:

SELECT 
    [Limit1].[EID] AS [EID], 
    [Limit1].[OwnerID] AS [OwnerID], 
    [Limit1].[TID] AS [TID], 
    [Limit1].[Title] AS [Title], 
    [Limit1].[EID1] AS [EID1]
    FROM ( SELECT TOP (10) 
        [Extent1].[EID] AS [EID], 
        [Extent2].[EID] AS [EID1], 
        [Extent2].[OwnerID] AS [OwnerID], 
        [Extent2].[TID] AS [TID], 
        [Extent3].[Title] AS [Title]
        FROM   [dbo].[tbl_EntryPoint] AS [Extent1]
        INNER JOIN [dbo].[tbl_Entry] AS [Extent2] ON [Extent1].[EID] = [Extent2].[EID]
        INNER JOIN [dbo].[tbl_Title] AS [Extent3] ON [Extent2].[TID] = [Extent3].[TID]
        WHERE [Extent2].[OwnerID] = @p__linq__0 OR [Extent3].[Title] = @p__linq__1
    )  AS [Limit1]

(This was generated with both a non-zero id and a non-empty title, and thus went down the very first if case, calling .Where with the expression that tests both the id and the title.)

How to remove where condition if the filter is empty in EF6

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator

Related