QueryableValues EF6 Edition

🤔💭 TLDR; By using QueryableValues, you can incorporate in-memory collections into your EF queries with outstanding performance and flexibility.

This library allows you to efficiently compose an IEnumerable<T> in your Entity Framework 6 (non-core) queries when using the SQL Server Provider. You can accomplish this by using the AsQueryableValues extension method that's available on the DbContext class. The query is processed in a single round trip to the server, in a way that preserves its execution plan, even when the values within the IEnumerable<T> are changed on subsequent executions. The supported types for T are: Byte, Int16, Int32, Int64, Guid, and String.

Highlights

• ✨ Enables the composition of in-memory data within your queries.
• 👌 Works with all versions of SQL Server supported by Entity Framework 6.
• ⚡ Automatically uses the most efficient strategy compatible with your SQL Server instance and configuration.
• ✅ Boasts over 700 tests for reliability and compatibility, giving you added confidence.

For a detailed explanation of the problem solved by QueryableValues, please continue reading here.

💡 This is a streamlined version of the original QueryableValues for Entity Framework Core, which I have adapted to provide some of its features on Entity Framework 6.

💡 Using Entity Framework Core? Then the original version of QueryableValues is what you need.

When Should You Use It?

The AsQueryableValues extension method is intended for queries that are dependent upon a non-constant sequence of external values. It also enables joins with in-memory data.

Your Support is Appreciated!

If you feel that this solution has provided you some value, please consider buying me a ☕.

Your ⭐ on this repository also helps! Thanks! 🖖🙂

Getting Started

Installation

QueryableValues EF6 Edition is distributed as a NuGet Package. You can install it using the command below in your NuGet Package Manager Console window in Visual Studio:

Install-Package BlazarTech.QueryableValues.EF6.SqlServer

How Do You Use It?

The AsQueryableValues extension method is provided by the BlazarTech.QueryableValues namespace; therefore, you must add the following using directive to your source code file for it to appear as a method of your DbContext instance:

using BlazarTech.QueryableValues;

💡 If you access your DbContext via an interface, you can also make the AsQueryableValues extension methods available on it by inheriting from the IQueryableValuesEnabledDbContext interface.

💡 You can configure the behavior of QueryableValues EF6 Edition by using the QueryableValuesConfigurator class.

Below are a few examples composing a query using the values provided by an IEnumerable<T>.

Examples

Using the Contains LINQ method:

// Sample values.
IEnumerable<int> values = Enumerable.Range(1, 10);

// This intermediary variable is needed to avoid a NotSupportedException
// with the message: "LINQ to Entities does not recognize the method...".
// Seems to be caused by the use of the Contains method.
var qvQuery = dbContext.AsQueryableValues(values);

// Example #1 (LINQ method syntax)
var myQuery1 = dbContext.TestData
    .Where(e => qvQuery.Contains(e.Id))
    .Select(i => new
    {
        i.Id,
        i.GuidValue
    })
    .ToList();
                
// Example #2 (LINQ query syntax)
var myQuery2 = (
    from e in dbContext.TestData
    where qvQuery.Contains(e.Id)
    select new
    {
        e.Id,
        e.GuidValue
    })
    .ToList();

Using the Join LINQ method:

// Sample values.
IEnumerable<int> values = Enumerable.Range(1, 10);

// Here we can make direct use of the AsQueryableValues extension without issues.

// Example #1 (LINQ method syntax)
var myQuery1 = dbContext.TestData
    .Join(
        dbContext.AsQueryableValues(values),
        e => e.Id,
        v => v,
        (e, v) => new
        {
            e.Id,
            e.GuidValue
        }
    )
    .ToList();

// Example #2 (LINQ query syntax)
var myQuery2 = (
    from e in dbContext.TestData
    join v in dbContext.AsQueryableValues(values) on e.Id equals v
    select new
    {
        e.Id,
        e.GuidValue
    })
    .ToList();

You can combine multiple set of values in a query:

// Sample values.
IEnumerable<int> values1 = Enumerable.Range(1, 5);
IEnumerable<int> values2 = Enumerable.Range(5, 5);

var qvQuery1 = dbContext.AsQueryableValues(values1);
var qvQuery2 = dbContext.AsQueryableValues(values2);

var myQuery = (
    from e in dbContext.TestData
    where
        qvQuery1.Contains(e.Id) ||
        qvQuery2.Contains(e.Id)
    select new
    {
        e.Id,
        e.GuidValue
    })
    .ToList();

You can mix and match these strategies in your query and make them as complex as you like.

That's it!

If you find this work useful please don't forget to ⭐ this repository.

Thanks! 🖖🙂