locked
Problem with 3 tables RRS feed

  • Question

  • I have 3 tables:

     

    Products

    - Id

    - Name

     

    TemplateMatching

    - TemplateRowId

    - ProductId

    - Value

     

    Template

    - Id

    - Property

     


    Sample data:

    Products:

    1    Car

    2    Bike

    3    Dog

     

    Template:

    1    HasEyes
    2    HasWheels
    3    CanMove

     

    TemplateMatching:

    ProductId    TemplateId    Value   

    1                 1                   0

    1                 2                   1

    1                 3                   1

    2                 1                   0

    2                 2                   1

    2                 3                   1

    3                 1                   1

    3                 2                   0

    3                 3                   1

     

     

    So, I want the products which have HasWheelsAND CanMove (not HasWheels OR CanMove).

     

    I tried very much query's, but no one is working Sad.

     

    The function must look like


    Public iQueryable<Product> GetProductsBasedOnTemplate(List<int> tempIdsToFilter)

    {

    var products = db.Products;

     

    ????

     

    return products;

    }

    Wednesday, April 16, 2008 5:30 PM

Answers

  • It's working

    Thanks to goog old T-Sql:

     

     

    Code Snippet

    public object GetProductsForWizard(int brandId, List<int> listWithChecks)

    {

    string whereStr = " BrandId = '" + brandId.ToString() + "' ";

     

    foreach (int i in listWithChecks)

    {

    whereStr += string.Format(" AND id IN (SELECT productId from TemplateProduct WHERE Value = 'Ja' AND TemplateRowId = {0}) ", i);

    }

     

    string query = "SELECT * FROM products WHERE " + whereStr;

     

    IEnumerable<Product> prods = db.ExecuteQuery<Product>(query);

     

    var endProducts = (from xx in prods

    select new

    {

    Phone = xx.Brand.BrandName + " " + xx.Type,

    Id = xx.id,

    ImageUrl = ConstValues.HttpImagesDirectory + xx.ImageNameMainPic

    });

     

    return endProducts.ToList();

    }

     

     

    Thanks to everybody!

     

     

    Thursday, April 24, 2008 3:44 PM

All replies

  • You could do a combination of Contains and All

     

    from p in db.Products

    where p.TemplateMatching.Select(tm => tm.TemplateRowId).All(id => tempIdsToFilter.Contains(id)

    select p;

     

    The query says that all template id's related to a product must be contained in the list.

     

     

     

    Thursday, April 17, 2008 2:38 AM
  • No, it isn't working

     

    List<int> listWithChecks = new List<int>();

    listWithChecks.Add(2);

    listWithChecks.Add(3);

     

    IQueryable<Product> prods = db.Products;

     

    prods = from p in prods

    where p.TemplateProducts.Select(tm => tm.TemplateRowId).All(id => listWithChecks.Contains(id))

    select p;

     

     

    Returns no products. There's also no check if the "Value" field == "1" (the Value field can be any string, 1 / 0 was just for the sample)

    Thursday, April 17, 2008 7:46 AM
  • I searched the whole day, but can't find anything. Someone with a little more Linq experience?

    Thursday, April 17, 2008 8:30 PM
  • try this

    var result = from tm in db.TemplateMatching
                     join p in db.Products on tm.ProductId equals p.Id
                     join t in db.Template on tm.TemplateId equals t.Id
                     where t.id = 2 and t.id = 3
                     select p
    Thursday, April 17, 2008 8:41 PM
  •  Pedro Rainho wrote:
    try this

    var result = from tm in db.TemplateMatching
                     join p in db.Products on tm.ProductId equals p.Id
                     join t in db.Template on tm.TemplateId equals t.Id
                     where t.id = 2 and t.id = 3
                     select p

     

    No, that won't work, because when you select t.id 2 AND t.id 3, there will no rows be found.

     

     

    The select must be like this:

     

    SELECT products

    WHERE productId IN (SELECT productId from TemplateProducts WHERE TemplateId = '2')

    AND productId IN (SELECT productId from TemplateProducts WHERE TemplateId = '3')

     

    But then in Linq, and working with the list... Smile

    Friday, April 18, 2008 8:37 AM
  • Your select It's not working and I think you mean these:

    SELECT productId from TemplateMatching
    WHERE productId IN (SELECT productId from TemplateMatching WHERE TemplateId = '2')
    AND productId IN (SELECT productId from TemplateMatching WHERE TemplateId = '3')

    if you try this you will get

    1
    2
    3
    1
    2
    3
    1
    2
    3

    and what you want is

    1    Car                                              
    2    Bike                                             

    these are the only ones that HasWheels AND CanMove

    So what you need to do in SQL is select all product don't HasEyes and value = 0

    the uery is

    select ProductId from TemplateMatching
    where templateid != 1 and value = 0

    all af those that dont has eyes are those that HasWheels AND CanMove

    and the result will be 1, 2

    and these is simples to implement with linq Smile

    Pedro Rainho
    Wednesday, April 23, 2008 2:12 PM
  • Try this out:

    Code Snippet

    public IQueryable<Product> GetProductsBasedOnTemplate(List<int> tempIdsToFilter)
    {

    var products = db.Products.Where(p =>
    tempIdsToFilter.All(id => p.TemplateMatchings.Any(tm =>
    tm.TemplateRowId == id)));

    return products;
    }


    Thursday, April 24, 2008 3:24 AM
  • I have tested the sample across a DB that I had created and I have an exception

    NotSupportedException
    Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator.

    Using Linq to SQL it seems that sequences in where can only use the Contains() operator, that was new to me Smile

    Pedro Rainho
    Thursday, April 24, 2008 9:30 AM
  •  Pedro Rainho wrote:
    Your select It's not working and I think you mean these:

    SELECT productId from TemplateMatching
    WHERE productId IN (SELECT productId from TemplateMatching WHERE TemplateId = '2')
    AND productId IN (SELECT productId from TemplateMatching WHERE TemplateId = '3')

    if you try this you will get

    1
    2
    3
    1
    2
    3
    1
    2
    3

    and what you want is

    1    Car                                              
    2    Bike                                             

    these are the only ones that HasWheels AND CanMove

    So what you need to do in SQL is select all product don't HasEyes and value = 0

    the uery is

    select ProductId from TemplateMatching
    where templateid != 1 and value = 0

    all af those that dont has eyes are those that HasWheels AND CanMove

    and the result will be 1, 2

    and these is simples to implement with linq

    Pedro Rainho

     

    Dear Pedro,

     

    Thanks for your reply! This would work, but my first post was just an example of a much more complex situation with thousands of records to filter Smile.

     

    Your SQL Code is almost correct:

    SELECT productId from Products
    WHERE productId IN (SELECT productId from TemplateMatching WHERE TemplateId = '2')
    AND productId IN (SELECT productId from TemplateMatching WHERE TemplateId = '3')

     

    It should be

    SELECT productId from Products
    WHERE Products.BrandId = '1'

    ForEach(int i in IntList)

    {

         AND productId IN (SELECT productId from TemplateMatching WHERE TemplateId = i)

    }

     

    But how to translate it into Linq???

    Thursday, April 24, 2008 9:51 AM

  • these part of code

    ForEach(int i in IntList)

    {

         AND productId IN (SELECT productId from TemplateMatching WHERE TemplateId = i)

    }



    seem to be dynamic, and I think this is a problem. I'm not see a easy solution Sad

    But there is a file named DynamicQuery.cs that came with linq samples and enables you to do things like these

    string whereStr = "Products.BrandId = \"1\"";

    ForEach(int i in IntList)

    {

        whereStr + =string.format(" AND productId IN (SELECT productId from TemplateMatching WHERE TemplateId = {0}) ", i);

    }



    after this you can do


    db.Products.Where(whereStr).Select(p => p);


    I know this is not the perfect solution but it's a solution  that my solve you problem


    Pedro Rainho






    Thursday, April 24, 2008 11:39 AM
  • IQueryable<Product> prods = db.Products.Where(b => b.BrandId == brandId);

     

    string whereStr = "";

     

    foreach (int i in listWithChecks)

    {

    whereStr += string.Format(" AND productId IN (SELECT productId from TemplateMatching WHERE Value = 'Ja' AND TemplateRowId = {0}) ", i);

    }

     

    prods = prods.Products.Where(whereStr).Select(p => p);

     

     

    Error 1 The type arguments for method 'System.Linq.Queryable.Where<TSource>(System.Linq.IQueryable<TSource>, System.Linq.Expressions.Expression<System.Func<TSource,bool>>)' cannot be inferred from the usage. Try specifying the type arguments explicitly. 

     

     

    Thursday, April 24, 2008 12:51 PM
  • Sorry I forget that the where method it's just for things like "ProductId = \"1\" &&Value = \"2\"" and not for this type of queries

    My solutions are out, the only solution I remember is create the SQL and execute the SQL with DataContext.ExecuteQuery

    Other thing can you post the tables used with all columns and values to test with your queries, and I will try reproduce your DB and use linq this weekend.

    Sorry

    Pedro Rainho
    Thursday, April 24, 2008 1:36 PM
  •  Pedro Rainho wrote:
    I have tested the sample across a DB that I had created and I have an exception

    NotSupportedException
    Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator.

    Using Linq to SQL it seems that sequences in where can only use the Contains() operator, that was new to me

    Pedro Rainho

    This is new to me too. I had never tried it out, but it seems like a logical query to follow. In any case, here's an alternative. Unfortunately, it requires that you iterate over the list of ids, but this is no different than making calls to the Contains method of the list.

     

    Code Snippet

    public IQueryable<Product> GetProductsBasedOnTemplate(List<int> tempIdsToFilter)

    {

    IQueryable<Product> productsQuery = db.Products;

     

    foreach (int id in tempIdsToFilter)

    productsQuery = productsQuery.Where(p =>

    p.TemplateMatchings.Any(tm =>

    tm.TemplateRowId == id));

     

    return productsQuery;

    }

     

    This effectively results in creating an initial query, and gradually filtering it with more conditions. The end result is that the final query contains an EXISTS clause for every Id in the filter list.

Thursday, April 24, 2008 1:46 PM
  • Martin,

    Your solution is not working well. For every ForEach the productsQuery is changed, so, you'll get the productsQuery only for the last id in tempIdsToFilter.

     

    Is there a possiblilty to fire good old T-Sql code, and return a product entity?

    Thursday, April 24, 2008 3:25 PM
  • It's working

    Thanks to goog old T-Sql:

     

     

    Code Snippet

    public object GetProductsForWizard(int brandId, List<int> listWithChecks)

    {

    string whereStr = " BrandId = '" + brandId.ToString() + "' ";

     

    foreach (int i in listWithChecks)

    {

    whereStr += string.Format(" AND id IN (SELECT productId from TemplateProduct WHERE Value = 'Ja' AND TemplateRowId = {0}) ", i);

    }

     

    string query = "SELECT * FROM products WHERE " + whereStr;

     

    IEnumerable<Product> prods = db.ExecuteQuery<Product>(query);

     

    var endProducts = (from xx in prods

    select new

    {

    Phone = xx.Brand.BrandName + " " + xx.Type,

    Id = xx.id,

    ImageUrl = ConstValues.HttpImagesDirectory + xx.ImageNameMainPic

    });

     

    return endProducts.ToList();

    }

     

     

    Thanks to everybody!

     

     

    Thursday, April 24, 2008 3:44 PM
  • Is there a possiblilty to fire good old T-Sql code, and return a product entity?

    use with your query
    DataContext.ExecuteQuery

    I'm try to adapt the code posted by martin to see if I can do anything with linq

    Pedro Rainho
    Thursday, April 24, 2008 4:10 PM
  • I understand the problem with my code. I had forgotten that the query's execution is delayed. So, while it did incrementally add EXISTS clauses to the query, the only variable against which it did compare was indeed the last ID filtered.

     

    Of course, injecting SQL code in there and calling ExecuteQuery does work, but it destroys the entire notion of making good use of delayed execution. If this is your goal, it would be pretty much equivalent to simply call the ToList method of the results sequence inside the foreach loop. Certainly not as efficient as running SQL code directly, but it will be type-safe, at the very least.

     

    UPDATE:

     

    I've stumbled on a way to get my query to work with only one extra line of code:

     

    Code Snippet

    public IQueryable<Product> GetProductsBasedOnTemplate(List<int> tempIdsToFilter)

    {

    IQueryable<Product> productsQuery = db.Products;

     

    foreach (int id in tempIdsToFilter)

    {

    int currentId = id;

    productsQuery = productsQuery.Where(p =>

    p.TemplateMatchings.Any(tm =>

    tm.TemplateRowId == currentId));

    }

     

    return productsQuery;

    }

     

    It would seem that declaring a temporary variable to hold the current id within the foreach loop causes the filter to work properly.

     

     

    Friday, April 25, 2008 6:52 PM