Multiple Left Joins and dynamic where

Question

I'm pretend to build a search page for a products Whit multiples form variables and integrates them to this query.. dynamically.. in where clauses of independents class like ProductoAtributo or others

And the d

T-SQL QUERY ( That I want )

SELECT 
    p.*, tpa.*, pa.*, tp.*
FROM 
    Producto p 
LEFT JOIN 
        ProductoAtributo pa ON pa.ProductoId=p.ProductoId
LEFT JOIN
        TipoProductoAtributo tpa ON tpa.AtributoId=pa.AtributoId AND tpa.TipoProductoId=p.TipoProductoId
LEFT JOIN
        TipoProducto tp ON p.TipoProductoId = tp.TipoProductoId
WHERE
        (pa.Valor = '3' AND pa.AtributoId=7) OR ( pa.Valor = '3' AND pa.AtributoId=6 )

Dynamic Where -- UPDATED

 Func<ProductoAtributo, bool> productoAtributoWhere = Pa => true;


            string CantidadDormitorios = "3";


            if (! String.IsNullOrEmpty(CantidadDormitorios))
            {
                productoAtributoWhere = Pa => (Pa.Valor == CantidadDormitorios && Pa.AtributoId == 7) || (Pa.Valor == CantidadDormitorios && Pa.AtributoId == 6);
            }

the attempting -> my closest aproach -- UPDATED

var producto = from P in db.Producto
                           join Tp in db.TipoProducto on P.TipoProductoId equals Tp.TipoProductoId into tpjoin
                           from TpJ in tpjoin.DefaultIfEmpty()
                           join Pa in db.ProductoAtributo on P.ProductoId equals Pa.ProductoId into pajoin
                           from PaJ in pajoin.AsQueryable<ProductoAtributo>().Where(productoAtributoWhere).DefaultIfEmpty()
                           join Tpa in db.TipoProductoAtributo on PaJ.AtributoId equals Tpa.AtributoId into tpajoin
                           from TpaJ in tpjoin.DefaultIfEmpty()
                           select new { P };
            var producto1 = producto.ToList();

THE ERROR

Error   3   'System.Collections.Generic.IEnumerable<SGI.Models.ProductoAtributo>' no contiene una definición para 'Where' y la mejor sobrecarga del método de extensión 'System.Linq.Enumerable.Where<TSource>(System.Collections.Generic.IEnumerable<TSource>, System.Func<TSource,int,bool>)' tiene algunos argumentos no válidos D:\Documentos\SGI6\SGI\Areas\Cotizacion\Controllers\CotizacionController.cs 55  22  Cotizacion

Error   4   Argumento 2: no se puede convertir de 'System.Linq.Expressions.Expression<System.Func<SGI.Models.ProductoAtributo,bool>>' a 'System.Func<SGI.Models.ProductoAtributo,int,bool>'.    D:\Documentos\SGI6\SGI\Areas\Cotizacion\Controllers\CotizacionController.cs 55  35  Cotizacion

Before the left joins -> "from TpJ in tpjoin.DefaultIfEmpty()" no errors... If i traslade the where to the " join Pa in db.ProductoAtributo" -> join Pa in db.ProductoAtributo.Where(productoAtributoWhere) No errors too ..however the t-sql query returns 3 register vs 26 the linq

without where ...linq and t-sql display the same results

Hope, you can help me .. Greetings

This the original query without the left joins UPDATED

var producto = from P in db.Producto
                           join Tp in db.TipoProducto on P.TipoProductoId equals Tp.TipoProductoId
                           join Pa in db.ProductoAtributo.Where(productoAtributoWhere) on P.ProductoId equals Pa.ProductoId
                           join Tpa in db.TipoProductoAtributo on Pa.AtributoId equals Tpa.AtributoId
                           select new { P };
            var producto1 = producto.ToList();

Could you please try running this with your .net localizations turned off, I cannot read the Exceptions in your code. Also I am having trouble working out what you mean by "dynamic" where. What is the logic for your `Where` Predicate? — Aron, Dec 20 '13 at 02:56
i think `pajoin` already not is `ProductoAtributo` collection, so you need change your Expression Type — Grundy, Dec 20 '13 at 05:53
My logic of Where was exposed here .. http://stackoverflow.com/a/1263587/493166 — Sk., Dec 20 '13 at 13:40
@DiegoOrtega do you have typing error in the query `from TpaJ in tpjoin.DefaultIfEmpty()` possibly you mean from `TpaJ in tpajoin.DefaultIfEmpty()`? — Grundy, Dec 20 '13 at 14:38

score 1 · Accepted Answer · answered Dec 20 '13 at 06:12

1

Your errors say that you try call method with wrong parameters, its because you predicate is Expression that using for IQueryable extensions, but when you use join..into clause you get not IQueryable, but IEnumeration, so for solve i see two paths

First: change type of predicate from Expression<Func<ProductoAtributo, bool>> to Func<ProductoAtributo, bool>

OR

Second: convert your groupped result to IQueryable i.e.

....
join Pa in db.ProductoAtributo on P.ProductoId equals Pa.ProductoId into pajoin
from PaJ in pajoin.AsQueryable().Where(productoAtributoWhere).DefaultIfEmpty()
....

answered Dec 20 '13 at 06:12

Grundy

13,356
3
35
55

Grundy, thanks for your answer the errors dissapears.. however attemting var producto1 = producto.ToList(); I Got a invalid Exeption 1025 internal error .NET Framework Data Provider. – Sk. Dec 20 '13 at 13:02
@DiegoOrtega, sorry, i do not quite understand what you mean, can you little more explain? – Grundy Dec 20 '13 at 13:05
i attemting to convert linq result with toList() method an throws a invalid Exception in the process... – Sk. Dec 20 '13 at 13:17
@DiegoOrtega, oh, i see that you apply all paths :-) try return back `Expression` for `productoAtributoWhere` declaration – Grundy Dec 20 '13 at 13:39
the Exeption dissapears..however the results are diferents between T-sql ( 3 rows ) vs Linq ( 26 rows ) – Sk. Dec 20 '13 at 13:50
@DiegoOrtega it seems like `where expression` does not work, are you sure that you try it not with expressions `Pa=>true` ? – Grundy Dec 20 '13 at 14:01
the where expression without the left join works like a charm ! – Sk. Dec 20 '13 at 14:03
@DiegoOrtega i have an idea! try swap `Where(productoAtributoWhere)` and `DefaultIfEmpty()` – Grundy Dec 20 '13 at 14:19
@DiegoOrtega why you mark its an answer? you solve your problem with different count of returns row for sql and linq? – Grundy Dec 21 '13 at 12:12
Yeah i solved that.. in my own answer ..i told was a problem of query in linq, combined with ... the location of the where.. without your help.. probably i wil don't finish ...read my answer.. – Sk. Dec 23 '13 at 18:05

score 1 · Answer 2 · answered Dec 20 '13 at 16:46

I installed Linqer... to translate t-sql to linq attached bin folder project, and connection to sql server...generate edml atuotmatically.. after that i got the correct linq to my query...

So far the error was put the productoAtributoWhere before the DefaultifEmpty(), when correct filter was after that...

Thanks @Grundy ..your's tips to the Expressions and AsQueryable was powerfully.

UPDATED CODE

    Expression<Func<ProductoAtributo, bool>> productoAtributoWhere = pa => true;


                string CantidadDormitorios = "";


                if (! String.IsNullOrEmpty(CantidadDormitorios))
                {
                    productoAtributoWhere = pa => (pa.Valor == CantidadDormitorios && pa.AtributoId == 7) || (pa.Valor == CantidadDormitorios && pa.AtributoId == 6);
                }

    var producto = from p in db.Producto
                                   join pa in db.ProductoAtributo on p.ProductoId equals pa.ProductoId into pa_join
                                   from pa in pa_join.DefaultIfEmpty().AsQueryable().Where( productoAtributoWhere )
                                   join tpa in db.TipoProductoAtributo
                                         on new { pa.AtributoId, p.TipoProductoId }
                                     equals new { tpa.AtributoId, tpa.TipoProductoId } into tpa_join
                                   from tpa in tpa_join.DefaultIfEmpty()
                                   join tp in db.TipoProducto on p.TipoProductoId equals tp.TipoProductoId into tp_join
                                   from tp in tp_join.DefaultIfEmpty()
select new
                           { p };
        var producto1 = producto.ToList();

Multiple Left Joins and dynamic where

2 Answers2