How to structure a partially dynamic LINQ statement?

Question

I currently have a method on my repository like this:

    public int GetMessageCountBy_Username(string username, bool sent)
    {
        var query = _dataContext.Messages.AsQueryable();

        if (sent)
            query = query.Where(x => x.Sender.ToLower() == username.ToLower());
        else
            query = query.Where(x => x.Recipient.ToLower() == username.ToLower());

        return query.Count();
    }

It currently builds one of two queries based on the sent boolean. Is this the best way to do this or is there a way to do this within the query itself? I want to check if x.Sender is equal to username if sent equals true. But I want to check if x.Recipient is equal to username if sent equals false.

I then want this LINQ expression to translate into SQL within Entity Framework, which I believe it is doing.

I just want to avoid repeating as much code as possible.

score 2 · Answer 1 · answered May 25 '11 at 04:54

2

Yes, I believe this is correct way to do it. Because it is easy to create complex queries without repeating whole parts of queries.

And your thinking about translating to SQL is correct too. But beware, this is done at the moment, when data or agregation is requested. In your case, the SQL will be generated and executed when you call Count().

answered May 25 '11 at 04:54

Euphoric

12,645
1
30
44

Awesome. Yeah as long as it's executed after the full query is constructed then I'm fine. I don't like returning `IQueryable` on my repositories. I prefer to give my repositories more verbose methods that do a little more work than regular repositories. That way no lazy loading issues later on :) – CatDadCode May 25 '11 at 05:43

score 2 · Accepted Answer · edited May 25 '11 at 17:02

2

You could do something like this :

public int GetMessageCountBy_Username(string username, bool sent)
 {
     Func<Message, string> userSelector = m => sent ? m.Sender : m.Recipient;
     var query =
     _dataContext.Messages.AsQueryable()
     .Where(x => userSelector(x).ToLower() == username.ToLower());
     return query.Count();
 }

Thus the choosing of the right user (the sender or the recipient) is done before the linq part, saving you from repeating it twice.

edited May 25 '11 at 17:02

CatDadCode

58,507
61
212
318

answered May 25 '11 at 07:43

Ssithra

710
3
8

Bravo! This makes more sense. Thank you. – CatDadCode May 25 '11 at 13:45
I did modify the function a little bit though. The lambda declaration has to come *before* the inline IF statement. – CatDadCode May 25 '11 at 17:03
Looks like I keep jumping the gun on accepting answers here. I should have tested this first. This definitely works, but not against IQueryable. The expression tree is supposed to be translated into a SQL expression. This means that this LINQ statement cannot invoke another function, all relevant data must be contained within the expression for SQL translation to work. A .ToList() will make it work, but will then sacrifice the ability to execute the query in the data source. Thanks for the answer though! – CatDadCode May 25 '11 at 18:31
It's really too bad it can't be done this way. I was a big fan of how simple the code looked, but I can't sacrifice passing the heavy lifting (i.e. paging & sorting) onto the SQL Server :( – CatDadCode May 25 '11 at 18:35
Oh sorry, I indeed missed that point. Too bad. :-( I'm not familiar with IQueryable and I didn't suspected this limitation. But there may be a hope left : what about if you change the Func for another type, nearer from the idea of a pure lambda ? (Can't unfortunately be more precise on something so unfamiliar to me, kinf of an Expression or something like this... ?) – Ssithra May 25 '11 at 19:40
An easier explanation of why this wont work is pretend you are taking a LINQ query and line by line you are translating that query into a SQL query. What do you do when that LINQ query calls a method somewhere else in the code? You can't translate a method (or Func) directly into a SQL query. Understand? So how the Func is structured doesn't matter. A LINQ query against IQueryable cannot call external resources or it will fail. – CatDadCode May 25 '11 at 19:49
@Chevex Err... I see the problem. Well, I humbly confess my lack of knowledge here. Nevertheless, 2 ideas seem worth considering (don't know in advance if they work, but you'll test it easily) : 1/ Try `Expression>` instead of just `Func` in order to deal with an expression tree rather than a pure delegate (I doubt it may work but who knows) 2/ Try embedding the choice in the Where clause, something like : `.Where(m => (sent ? m.Sender : m.Recipient).ToLower() == username.ToLower())` This is my last hope to provide a help of any use ! :-) – Ssithra May 26 '11 at 08:56
The Where clause one may work. Not sure about Expression as I'm not too familiar with it. I will try it later today. Thanks for the ideas :) – CatDadCode May 26 '11 at 13:20
Well I can tell you that doing it within the where statement worked beautifully. – CatDadCode May 26 '11 at 13:56
Really glad of having been able to help, and to have learnt some new pieces of knowledge from this exchange with you. (Please tell me about the Expression stuff if you eventually test it :-)) – Ssithra May 26 '11 at 14:32
I'm testing it now and it is far more complicated than I realized. I'm going to start another SO question about it. I'll link it here when done. – CatDadCode May 26 '11 at 14:49
http://stackoverflow.com/questions/6140835/how-to-turn-this-func-into-an-expression – CatDadCode May 26 '11 at 15:09
@Chevex Thank you, it's a really great and interesting follow up ! I was suspecting such an opening, you brilliantly finalized it with most valuable explanations of the process. – Ssithra May 26 '11 at 19:29

How to structure a partially dynamic LINQ statement?

2 Answers2