CODEWITHSUNDEEP
linqlinq-to-sqllinq-expressions
Michael Sagalovich
Michael Sagalovich

Reputation: 2549

Test LINQ to SQL expression

I am writing an application that works with MS SQL database via LINQ to SQL. I need to perform filtering sometimes, and occasionally my filtering conditions are too complicated to be translated into SQL query. While I am trying to make them translatable, I want my application to at least work, though slow sometimes.

LINQ to SQL data model is hidden inside repositories, and I do not want to provide several GetAll method overloads for different cases and be aware of what overload to use on upper levels. So I want to test my expression inside repository to be translatable and, if no, perform in-memory query against the whole data set instead of throwing NotSupportedException on query instantiating.

This is what I have now:

IQueryable<TEntity> table = GetTable<TEntity>();
IQueryable<TEntity> result;
try
{
    result = table.Where(searchExpression);

    //this will test our expression 
    //consuming as little resources as possible (???)        
    result.FirstOrDefault(); 
}
catch (NotSupportedException)
{
    //trying to perform in-memory search if query could not be constructed
    result = table
        .AsEnumerable()
        .Where(searchExpression.Compile())
        .AsQueryable();
}
return result;

searchExpression is Expression<Func<TEntity, bool>>

As you see, I am using FirstOrDefault to try to instantiate the query and throw the exception if it cannot be instantiated. However, it will perform useless database call when the expression is good. I could use Any, Count or other method, and it may well be a bit less expensive then FirstOrDefault, but still all methods that come to my mind make a costly trip to database, while all I need is to test my expression.

Is there any alternative way to say whether my expression is 'good' or 'bad', without actual database call?

UPDATE:

Or, more generally, is there a way to tell LINQ to make in-memory queries when it fails to construct SQL, so that this testing mechanism would not be needed at all?

Upvotes: 2

Views: 522

Answers (2)

sgmoore
sgmoore

Reputation: 16077

Instead of 

   result.FirstOrDefault();

would it be sufficient to use 

    string sqlCommand = dataContext.GetCommand(result).CommandText;

?

If the expression does not generate valid Sql, this should throw a NotSupportedException, but it does not actually execute the sqlCommand.

Upvotes: 2

thekip
thekip

Reputation: 3768

I think this will solve your problem:

IQueryable<TEntity> table = GetTable<TEntity>();  
IQueryable<TEntity> result;
try
{
    return table.Where(searchExpression).ToList();
}
catch (NotSupportedException)
{
    //trying to perform in-memory search if query could not be constructed
    return table
        .AsEnumerable()
        .Where(searchExpression.Compile())
        .ToList();
}

So the method returns is the expression is converted to valid SQL. Otherwise it catches the exception and runs the query in memory. This should work but it doesn't answer your question if it's possible to check if a specific searchExpression can be converted. I don't think such a thing exists.

Upvotes: 1

Related Questions