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