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 — madreflection
– madreflection, Commented Jan 4, 2023 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
– Mike93041, Commented Jan 4, 2023 at 20:44
@madreflection Not much, none of the answers to that question would help in my case, but thanks for the help. — GabrielNexT
– GabrielNexT, Commented Jan 4, 2023 at 21:10
@Mike93041 No Mike, I need to put one at a time, to induce my database to use the indexes. Thanks. — GabrielNexT
– GabrielNexT, Commented Jan 4, 2023 at 21:11
Read it more thoroughly. It's all there. This answer shows how to accept the predicate as a parameter, and this answer 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
– madreflection, Commented Jan 4, 2023 at 21:12

Svyatoslav Danyliv · Accepted Answer · 2023-01-04 21:11:16Z

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 4, 2023 at 21:11

answered Jan 4, 2023 at 20:48

Svyatoslav Danyliv

28.2k4 gold badges24 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

GabrielNexT Over a year ago

Hi, thanks for the help, DbSet's Where function doesn't take an Expression as a parameter, only Func, so I passed Func<Guest, bool> as a parameter, but that also resulted in an error. Trace

Svyatoslav Danyliv Over a year ago

Will update answer with more clear scenario.

Guru Stron Over a year ago

@GabrielNexT "DbSet's Where function doesn't take an Expression as a parameter, only Func," - sounds like something which is not true =)

GabrielNexT Over a year ago

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

Collectives™ on Stack Overflow

Pass where condition as parameter using LINQ expression query

Solution

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Solution

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related