How to structure a partially dynamic LINQ statement?

I currently have a method on my repository like this:

    public int GetMessageCountBy_Username(string userna开发者_高级运维me, 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.

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.

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