LINQ-to-SQL and Extension method in subQuery

Question

My extension method is:

public static IEnumerable<T> FilterCultureSubQuery<T>(this Table<T> t)
    where T : class
    {
      return t;
    }

I tried to use it in this query

var test = from p in t.Products
             select new
             {
               Allo = p,
               Allo2 = (from pl in t.ProductLocales.FilterCultureSubQuery()
                        select pl)
             };

What is supposed to be the signature of my method extension? I always get this error:

Method 'System.Collections.Generic.IEnumerable`1[ProductLocale] FilterCultureSubQuery[ProductLocale](System.Data.Linq.Table`1[ProductLocale])' has no supported translation to SQL.

I also tried this signature:

public static IQueryable<T> FilterCultureSubQuery<T>(this Table<T> t)
    where T : class
    {
      return t;
    }

And I got this error:

Method 'System.Linq.IQueryable`1[ProductLocale] FilterCultureSubQuery[ProductLocale](System.Data.Linq.Table`1[ProductLocale])' has no supported translation to SQL.

Thanks

Answer 1

The signature of your method is fine. The problem is, as stated, it "has no supported translation to SQL".

DLINQ is attempting to convert that statement into a line of SQL which it will send to the database. That method has no translation.

I'd suggest rewriting the filter using a Where clause.

Answer 2

There is nothing wrong with your Extension method.

You get that exception because you are trying to use your custom method in a LINQ-To-SQL query, and LINQ-To-SQL does not know of a translation to SQL for your method. So it cannot construct a SQL query from your LINQ expression.

The solution would be to get the data first, then apply your transformation.

Answer 3

When i use my extension method in a simple query, it's working, but when i use it in a sub query it's not working. Any solutions ?

Working

var test = from pl in t.ProductLocales.FilterCultureSubQuery()  select pl;

Not Working

var test = from p in t.Products
           select new
           {
             Allo = p,
             Allo2 = (from pl in t.ProductLocales.FilterCultureSubQuery()
                      select pl)
           };

I create a new extension method and rewrite the expression tree of the query.

var test = (from p in t.Products
               select new
               {
                 Allo = p,
                 Allo2 = (from pl in t.ProductLocales.FilterCultureSubQuery()
                          select pl)
               }).ArrangeExpression();

LINQ-TO-SQL have difficulty to use extension method in subquery. With a rewrite expression extension method everyting working fine.

Any other solutions ?