How to: Perform Bulk Select Based on an In-memory Collection

Telerik Data Access gives you the ability to retrieve multiple entities based on an in-memory collection. This can be done by using Contains or ContainsExtended extension methods in your LINQ queries. Both of these methods are executed on the database engine and will return the same result, but the execution itself differs. In this article we will show you how to use the methods and what are their specifics.

• Contains
  • Smart Switching
• ContainsExtended
• Argument Operations Considerations

Contains

Contains is an extension method to IEnumerable and is part of the System.Linq namespace. It can be used in LINQ queries in order to retrieve multiple objects based on values contained in an in-memory collection. Telerik Data Access provides special handling for this method in order to be executed on the database server. For example, consider a scenario where you need to retrieve all available cars in a specific range of models from the SofiaCarRental database. You can do so using the Contains method:

List<string> carModels = new List<string>();
carModels.Add("Golf");
carModels.Add("Corsa");
carModels.Add("Civic");
carModels.Add("HR-V");
carModels.Add("X5");
carModels.Add("Taureg");

    List<Car> availableCars = context.Cars
    .Where(car => carModels.Contains(car.Model) && car.Available == true)
    .ToList();

Dim carModels As New List(Of String)
carModels.Add("Golf")
carModels.Add("Corsa")
carModels.Add("Civic")
carModels.Add("HR-V")
carModels.Add("X5")
carModels.Add("Taureg")

Dim availableCars As List(Of Car) = context.Cars.
    Where(Function(car) carModels.Contains(car.Model) AndAlso car.Available = True).
    ToList()

The result of the query will be all available cars with model that can be found in the carModels collection. The filtering is done on the database side and the following SQL query is generated and executed:

SELECT *
FROM [Cars] a 
WHERE (a.[Model] IN (@p0,@p1,@p2,@p3,@p4,@p5)) AND a.[Available] = 1 
ORDER BY COL1  

As you can see, Contains uses IN clause for the filtering which results in fast queries to the database.

Smart Switching

While the approach of using IN clause for filtering provides high performance, it is not without its limitations. The various backends supported by Telerik Data Access have different limits for the number of SQL Parameters and values in an IN clause. In case one of these limits is hit by the query which uses Contains, if the backend supports it the method will automatically switch to using temporary tables for the filtering. This is done in order to prevent the database engine from throwing any unexpected errors.

The following table illustrates the maximum number of SQL Parameters and values in an IN clause for the different database engines:

*This upper limit is defined in Telerik Data Access and not by the backend itself.

Telerik Data Access provides you with the means to programmatically access the maximum number for SQL parameters and IN clause values. This is done through the BackendInformation class.

Using the Contains method in a query against a database engine which does not support temporary tables will result in a System.NotSupportedException if the SQL parameters or IN values limit is reached. Currently such backends are VistaDb and Firebird. You can verify whether the backend in use supports temporary tables by using the SupportsTemporaryTables property of the BackendInformation class

ContainsExtended

ContainsExtended is an extension method for IEnumerable and part of the Telerik.OpenAccess namespace. The method is available in the Telerik.OpenAccess.35.Extensions assembly and it has the following signature:

public static bool ContainsExtended<T>(this IEnumerable<T> source, T element);

The ContainsExtended method has the same functionality as Contains, but it only uses temporary tables to filter the objects which are to be retrieved. Again, consider the scenario where you need to retrieve all available cars in a specific range of models from the SofiaCarRental database:

List<string> carModels = new List<string>();
carModels.Add("Golf");
carModels.Add("Corsa");
carModels.Add("Civic");
carModels.Add("HR-V");
carModels.Add("X5");
carModels.Add("Taureg");

    List<Car> availableCars = context.Cars
    .Where(car => carModels.ContainsExtended(car.Model) && car.Available == true)
    .ToList();

Dim carModels As New List(Of String)
carModels.Add("Golf")
carModels.Add("Corsa")
carModels.Add("Civic")
carModels.Add("HR-V")
carModels.Add("X5")
carModels.Add("Taureg")

Dim availableCars As List(Of Car) = context.Cars.
    Where(Function(car) carModels.ContainsExtended(car.Model) AndAlso car.Available = True).
        ToList()

Due to the higher performance of IN clauses, we recommend using the Contains method. ContainsExtended should be used only when your scenario would benefit from the exclusive usage of temporary tables for the filtering of the objects which will be retrieved.

This LINQ query will retrieve all available cars with model that can be found in the carModels list. The filtering will be done entirely on the database engine using temporary tables. The following SQL query will be generated and executed by Telerik Data Access to do so:

CREATE TABLE [#TMP9220B07AF7A14D50B1EE055E87] ([Model] varchar(50)) 
INSERT INTO [#TMP9220B07AF7A14D50B1EE055E87] ([Model]) VALUES (@p0)  --6 param rows
SELECT *
FROM [Cars] a 
WHERE EXISTS (SELECT 1234567 
              FROM [#TMP9220B07AF7A14D50B1EE055E87] b 
              WHERE a.[Model] = b.[Model]) AND a.[Available] = 1)
ORDER BY COL1

The performance of this approach would be lower as compared to using IN clause, but it will also scale more fluidly and therefore be more predictable with variable size of the in-memory collection.

Using the ContainsExtended method in a query against a database engine which does not support temporary tables will result in a System.NotSupportedException. Currently such backends are VistaDb and Firebird. You can verify whether the backend in use supports temporary tables by using the SupportsTemporaryTables property of the BackendInformation class

Argument Operations Considerations

There are certain things you need to consider when performing and additional operation over the argument passed to Contains or ContainsExtended in the LINQ query:

• The operations should be supported on the database engine that is currently in use. Otherwise the methods will throw System.NotSupportedException. As the GetHashCode method is not supported on the database side, the following query would be an example:

  var cars = context.Cars.Where(car => carIdsAsHashCodes
      .ContainsExtended(car.CarID.GetHashCode())).ToList();
  

  Dim cars = context.Cars.Where(Function(car) carIdsAsHashCodes _
      .ContainsExtended(car.CarID.GetHashCode())).ToList()
  

• The operations should return the same CLR type as the type of the persistent property over which they operate. Otherwise, the methods will throw System.NotSupportedException. The following query is an example where the Length method returns a different type than the one of the Make property:

  var cars = context.Cars.Where(car => viableLenghts
      .ContainsExtended(car.Make.Length)).ToList();
  

  Dim cars = context.Cars.Where(Function(car) viableLenghts _
      .ContainsExtended(car.Make.Length)).ToList()