Pass where condition as parameter using LINQ expression query

Question

Initially I have the following query in a function. This query is used in the join in another query, which I didn't add due to its size.

var latestGuestRegistration = from c in Context.Guest
                group c by c.PersonId
                into cc
                select new { PersonId = cc.Key, CreationTime = cc.Max(a => a.CreationTime) };

I would like to add a filter, however I could filter by name or email. So I would like to pass this filter as a function parameter.

I couldn't do this using C#'s inline SQL syntax, so I tried converting to LINQ:

var tmp = Context.GuestRegistrations
                .Where(filter) // Here I filter by email or name
                .GroupBy(x => x.PersonId)
                .Select(cc => new { PersonId = cc.Key, CreationTime = cc.Max(a => a.CreationTime) })
                .AsQueryable();

According to Rider, both implementations have the same data type, which is Queryable<{ID, CreationTime}>

// Joins above
 join lt in tmp on
                new { guestRegistration.PersonId, guestRegistration.CreationTime } equals
                new { lt.PersonId, lt.CreationTime }
// Joins below

When this join is executed using my LINQ, it seems that it already returns the data, while the original solution enters as a subquery. How can I add a filter to my original query?

In the end, I would like something more or less like this:

Expression<Func<GuestRegistration,bool>> wherePredicate = registration => registration.FullName = "Gabriel";

var latestGuestRegistration = from c in Context.GuestRegistrations
                where wherePredicate
                group c by c.PersonId
                into cc
                select new { PersonId = cc.Key, CreationTime = cc.Max(a => a.CreationTime) };

Solution

Thanks to everyone who responded and commented, below is a summary of the final code.

private IQueryable<...> GetSearch(Expression<Func<G, bool>> filter) {
    var filteredG= Context.G.Where(filter);
    var latestG = from c in filteredG
                group c by c.PersonId
                into cc
                select new { PersonId = cc.Key, CreationTime = cc.Max(a => a.CreationTime) };

    var bigQuery = from ... join latestG ...
}
    
GetSearch(x => x.Fullname == "Gabriel")

Does this answer your question? [Linq: adding conditions to the where clause conditionally](https://stackoverflow.com/questions/10884651/linq-adding-conditions-to-the-where-clause-conditionally) — madreflection, Jan 04 '23 at 20:42
.Where(registration => registration.Email == filter || registration.FullName == filter) Is this what you refer to? Modify alike maybe triming and lowering then checking if it's contained. — Mike93041, Jan 04 '23 at 20:44
@madreflection Not much, none of the answers to that question would help in my case, but thanks for the help. — GabrielNexT, Jan 04 '23 at 21:10
@Mike93041 No Mike, I need to put one at a time, to induce my database to use the indexes. Thanks. — GabrielNexT, Jan 04 '23 at 21:11
Read it more thoroughly. It's all there. [This answer](https://stackoverflow.com/a/35930973) shows how to accept the predicate as a parameter, and [this answer](https://stackoverflow.com/a/10884757) shows you how to add the query to an existing query before executing it (it doesn't need to be conditional in your case, so the `if` can be ignored). You can't do it with LINQ syntax; you have to do it with extension method syntax. It *does* help in your case, but you have to make some changes. — madreflection, Jan 04 '23 at 21:12

Svyatoslav Danyliv · Accepted Answer · 2023-01-04T21:11:16.583

4

Some expressions cannot be injected via Query syntax, but you can mix it with Method Chain syntax:

Expression<Func<GuestRegistration,bool>> wherePredicate = registration => registration.FullName = "Gabriel";

var guestRegistrations = Context.GuestRegistrations
     .Where(wherePredicate);

var latestGuestRegistration = 
    from c in guestRegistrations
    group c by c.PersonId
    into cc
    select new { PersonId = cc.Key, CreationTime = cc.Max(a => a.CreationTime) };

edited Jan 04 '23 at 21:11

answered Jan 04 '23 at 20:48

Svyatoslav Danyliv

21,911
3
16
32

Hi, thanks for the help, DbSet's Where function doesn't take an Expression as a parameter, only Func, so I passed Func as a parameter, but that also resulted in an error. [Trace](https://pastebin.com/raw/gKjxkeLd) – GabrielNexT Jan 04 '23 at 21:06
1

Will update answer with more clear scenario. – Svyatoslav Danyliv Jan 04 '23 at 21:09
2

@GabrielNexT _"DbSet's Where function doesn't take an Expression as a parameter, only Func,"_ - sounds like something which is not true =) – Guru Stron Jan 04 '23 at 21:17
@SvyatoslavDanyliv It's true, I was wrong, I did it using Expression and now the error was different, but it seems to be a particularity of my project, I'm investigating. Thanks for your help, I think your answer is correct. If your solution really works, I'll mark your answer as correct, it will help a lot of people. – GabrielNexT Jan 04 '23 at 21:27

Pass where condition as parameter using LINQ expression query

Solution

1 Answers1