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);
}
1
0
1/19/2017 8:13:22 PM

Accepted Answer

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.)

1
1/19/2017 8:49:43 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow