CODEWITHSUNDEEP
c#entity-frameworkentity-framework-5
Neil
Neil

Reputation: 5239

Querying with many (~100) search terms with Entity Framework

I need to do a query on my database that might be something like this where there could realistically be 100 or more search terms.

public IQueryable<Address> GetAddressesWithTown(string[] towns)
{
    IQueryable<Address> addressQuery = DbContext.Addresses;
    addressQuery.Where( x => towns.Any( y=> x.Town == y ) );
    return addressQuery;
}

However when it contains more than about 15 terms it throws and exception on execution because the SQL generated is too long.

Can this kind of query be done through Entity Framework?

What other options are there available to complete a query like this?

Upvotes: 1

Views: 297

Answers (3)

Eamon Nerbonne
Eamon Nerbonne

Reputation: 48096

You've broadly got two options:

  • You can replace .Any with a .Contains alternative.
  • You can use plain SQL with table-valued-parameters.

Using .Contains is easier to implement and will help performance because it translated to an inline sql IN clause; so 100 towns shouldn't be a problem. However, it also means that the exact sql depends on the exact number of towns: you're forcing sql-server to recompile the query for each number of towns. These recompilations can be expensive when the query is complex; and they can evict other query plans from the cache as well.

Using table-valued-parameters is the more general solution, but it's more work to implement, particularly because it means you'll need to write the SQL query yourself and cannot rely on the entity framework. (Using ObjectContext.Translate you can still unpack the query results into strongly-typed objects, despite writing sql). Unfortunately, you cannot use the entity framework yet to pass a lot of data to sql server efficiently. The entity framework doesn't support table-valued-parameters, nor temporary tables (it's a commonly requested feature, however).

A bit of TVP sql would look like this select ... from ... join @townTableArg townArg on townArg.town = address.town or select ... from ... where address.town in (select town from @townTableArg).

You probably can work around the EF restriction, but it's not going to be fast and will probably be tricky. A workaround would be to insert your values into some intermediate table, then join with that - that's still 100 inserts, but those are separate statements. If a future version of EF supports batch CUD statements, this might actually work reasonably.

Almost equivalent to table-valued paramters would be to bulk-insert into a temporary table and join with that in your query. Mostly that just means you're table name will start with '#' rather than '@' :-). The temp table has a little more overhead, but you can put indexes on it and in some cases that means the subsequent query will be much faster (for really huge data-quantities).

Unfortunately, using either temporary tables or bulk insert from C# is a hassle. The simplest solution here is to make a DataTable; this can be passed to either. However, datatables are relatively slow; the over might be relevant once you start adding millions of rows. The fastest (general) solution is to implement a custom IDataReader, almost as fast is an IEnumerable<SqlDataRecord>.

By the way, to use a table-valued-parameter, the shape ("type") of the table parameter needs to be declared on the server; if you use a temporary table you'll need to create it too.

Some pointers to get you started:

Upvotes: 0

TomTom
TomTom

Reputation: 62127

Sorry, are we talking about THIS EXACT SQL?

In that case it is a very simple "open your eyes thing".

There is a way (contains) to map that string into an IN Clause, that results in ONE sql condition (town in ('','',''))

Let me see whether I get this right:

addressQuery.Where( x => towns.Any( y=> x.Town == y ) );

should be

addressQuery.Where ( x => towns.Contains (x.Town)

The resulting SQL will be a LOT smaller. 100 items is still taxing it - I would dare saying you may have a db or app design issue here and that requires a business side analysis, I have not me this requirement in 20 years I work with databases.

Upvotes: 3

scartag
scartag

Reputation: 17680

This looks like a scenario where you'd want to use the PredicateBuilder as this will help you create an Or based predicate and construct your dynamic lambda expression.

This is part of a library called LinqKit by Joseph Albahari who created LinqPad.

 public IQueryable<Address> GetAddressesWithTown(string[] towns)
{
  var predicate = PredicateBuilder.False<Address>();

  foreach (string town in towns)
  {
    string temp = town;
    predicate = predicate.Or (p => p.Town.Equals(temp));
  }

  return DbContext.Addresses.Where (predicate);
}

Upvotes: 1

Related Questions