Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

LINQ to Entities / LINQ to SQL: switching from server (queryable) to client (enumerable) in the middle of a query comprehension?

In many cases, I want to do some filtering (and sometimes projection) on the server side and then switch to client-side for operations that the LINQ provider doesn't natively support.

The naive approach (which is basically what I do now) is to just break it up into multiple queries, similar to:

var fromServer = from t in context.Table
                 where t.Col1 = 123
                 where t.Col2 = "blah"
                 select t;

var clientSide = from t in fromServer.AsEnumerable()
                 where t.Col3.Split('/').Last() == "whatever"
                 select t.Col4;

However, there are many times where this is more code/trouble than it's really worth. I'd really like to do a 'switch to client side' in the middle. I've tried various methods of using a query continuation, but after doing a 'select t into foo' at the end of the first query, foo is still an individual item, not the collection, so I can't AsEnumerable() it.

My goal is to be able write something more like:

var results = from t in context.Table
              where t.Col1 = 123
              where t.Col2 = "blah"
              // Magic happens here to switch to the client side
              where t.Col3.Split('/').Last() == "whatever"
              select t.Col4;
like image 999
James Manning Avatar asked Jan 28 '11 02:01

James Manning


People also ask

Which Entities can LINQ use to perform queries?

LINQ to Entities queries are comprised of LINQ standard query operators (such as Select, Where, and GroupBy) and expressions (x > 10, Contact. LastName, and so on). LINQ operators are not defined by a class, but rather are methods on a class.

What is the difference between LINQ and Entity Framework?

LINQ to SQL allow you to query and modify SQL Server database by using LINQ syntax. Entity framework is a great ORM shipped by Microsoft which allow you to query and modify RDBMS like SQL Server, Oracle, DB2 and MySQL etc. by using LINQ syntax.

How are LINQ queries executed?

LINQ queries are always executed when the query variable is iterated over, not when the query variable is created. This is called deferred execution. You can also force a query to execute immediately, which is useful for caching query results.

What are LINQ queries in C#?

Language-Integrated Query (LINQ) is the name for a set of technologies based on the integration of query capabilities directly into the C# language. Traditionally, queries against data are expressed as simple strings without type checking at compile time or IntelliSense support.


2 Answers

Okay, firstly you absolutely should not use the code here. It was written by trained stunt-hamsters who have been trained not to throw up when dealing with this code of this nature.

You should absolutely pick one of the options you know about:

  • Use a "temporary" variable (if you can statically type that variable as IEnumerable<T> then you don't need the call to AsEnumerable - that won't work if you've got an anonymous type as the element type of course)
  • Use brackets for a call to AsEnumerable
  • Use the "fluent" or "dot notation" syntax to make the AsEnumerable call fit in.

However, you can do a bit of magic, using the way that query expressions are translated. You just need to make one of the standard query operators with a representation in query expressions have a different translation. The simplest option here is probably "Where". Just write your own extension method taking an IQueryable<T> and a Func<T, SomeType> where SomeType isn't bool, and you're away. Here's an example, first of the hack itself and then a sample use of it...

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;

public static class QueryHacks
{
    public static readonly HackToken TransferToClient = HackToken.Instance;

    public static IEnumerable<T> Where<T>(
        this IQueryable<T> source,
        Func<T, HackToken> ignored)
    {
        // Just like AsEnumerable... we're just changing the compile-time
        // type, effectively.
        return source;
    }

    // This class only really exists to make sure we don't *accidentally* use
    // the hack above.
    public class HackToken
    {
        internal static readonly HackToken Instance = new HackToken();
        private HackToken() {}
    }
}

public class Test
{
    static void Main()
    {
        // Pretend this is really a db context or whatever
        IQueryable<string> source = new string[0].AsQueryable();

        var query = from x in source
                    where x.StartsWith("Foo") // Queryable.Where
                    where QueryHacks.TransferToClient
                    where x.GetHashCode() == 5 // Enumerable.Where
                    select x.Length;
    }
}
like image 80
Jon Skeet Avatar answered Nov 15 '22 19:11

Jon Skeet


Of course, if you were using the normal method syntax, this would be no problem:

var results = context.Table
              .Where(t => t.Col1 == 123)
              .Where(t => t.Col2 == "blah")
              .AsEnumerable()
              .Where(t => t.Col3.Split('/').Last() == "whatever")
              .Select(t => t.Col4);

If you insist on using the query syntax, you won’t get around using some parentheses, but otherwise, you can certainly still do the same:

var results = from t in (
                  from t in context.Table
                  where t.Col1 == 123
                  where t.Col2 == "blah"
                  select t
              ).AsEnumerable()
              where t.Col3.Split('/').Last() == "whatever"
              select t.Col4;

Reusing the variable name t does not cause any problems; I tested it.

like image 44
Timwi Avatar answered Nov 15 '22 20:11

Timwi