Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


These support forums are now closed for new topics and comments.
Please head on over to http://eureka.ucommerce.net/ for support.

  • Matt Brailsford 4125 posts 22222 karma points MVP 9x c-trib
    May 24, 2010 @ 12:10
    Matt Brailsford
    0

    Querying products

    Hi Guys,

    Can anybody see an issue with the following query?

    from prod in Product.All()
    join desc in ProductDescription.All() on new { pid = prod.ProductId, cc = _currentCultureCode } equals new { pid = desc.ProductId, cc = desc.CultureCode }
    where desc.DisplayName.StartsWith(!string.IsNullOrEmpty(Param1) ? Param1 : desc.DisplayName) && (
        from prop in ProductProperty.All()
        join field in ProductDefinitionField.All() on prop.ProductDefinitionFieldId equals field.ProductDefinitionFieldId
        where field.Name == "Medium" && prop.Value == ((!string.IsNullOrEmpty(Param2) && Param2 != "Any") ? Param2 : prop.Value)
        select prop.ProductId
    ).Contains(prod.ProductId) && (
        from prop in ProductProperty.All()
        join field in ProductDefinitionField.All() on prop.ProductDefinitionFieldId equals field.ProductDefinitionFieldId
        where field.Name == "Format" && prop.Value == ((!string.IsNullOrEmpty(Param3) && Param3 != "Any") ? Param3 : prop.Value)
        select prop.ProductId
    ).Contains(prod.ProductId) && (
        from prop in ProductProperty.All()
        join field in ProductDefinitionField.All() on prop.ProductDefinitionFieldId equals field.ProductDefinitionFieldId
        where field.Name == "Presentation" && prop.Value == ((!string.IsNullOrEmpty(Param4) && Param4 != "Any") ? Param4 : prop.Value)
        select prop.ProductId
    ).Contains(prod.ProductId)
    select prod; 

    It currently throws an exception

    Sequence contains more than one matching element

    I'm trying to pull back products with certain properies, ie Products with a "Medium" property of X AND a "Format" property of Y AND a "Presentation" property Z

  • Matt Brailsford 4125 posts 22222 karma points MVP 9x c-trib
    May 25, 2010 @ 11:14
    Matt Brailsford
    0

    Well, it's not pretty, but it's the only way I could get it to work:

    var q3 = (from prop in ProductProperty.All()
         join field in ProductDefinitionField.All() on prop.ProductDefinitionFieldId equals field.ProductDefinitionFieldId
         where field.Name == "Medium" && prop.Value == ((!string.IsNullOrEmpty(Param2) && Param2 != "Any") ? Param2 : prop.Value)
         select prop.ProductId).ToList();

    q3.Add(-1); // Prevents NHibernate from throwing an exception if list is empty

    var q4 = (from prop in ProductProperty.All()
         join field in ProductDefinitionField.All() on prop.ProductDefinitionFieldId equals field.ProductDefinitionFieldId
         where field.Name == "Format" && prop.Value == ((!string.IsNullOrEmpty(Param3) && Param3 != "Any") ? Param3 : prop.Value)
         select prop.ProductId).ToList();

    q4.Add(-1); // Prevents NHibernate from throwing an exception if list is empty

    var q5 = (from prop in ProductProperty.All()
          join field in ProductDefinitionField.All() on prop.ProductDefinitionFieldId equals field.ProductDefinitionFieldId
          where field.Name == "Presentation" && prop.Value == ((!string.IsNullOrEmpty(Param4) && Param4 != "Any") ? Param4 : prop.Value)
          select prop.ProductId).ToList();

    q5.Add(-1); // Prevents NHibernate from throwing an exception if list is empty

    var q6 = from prod in Product.All()
           join desc in ProductDescription.All() on new { pid = prod.ProductId, cc = _currentCultureCode } equals new { pid = desc.ProductId, cc = desc.CultureCode }
           where desc.DisplayName.StartsWith(!string.IsNullOrEmpty(Param1) ? Param1 : desc.DisplayName) &&
           q3.Contains(prod.ProductId) &&
           q4.Contains(prod.ProductId) &&
           q5.Contains(prod.ProductId) &&
           prod.DisplayOnSite == true
           select prod;
  • Søren Spelling Lund 1797 posts 2786 karma points
    May 28, 2010 @ 14:56
    Søren Spelling Lund
    0

     

    Finally got some time to sit down and write a little bit of code. If I'm not mistaken I believe you can achieve the same thing with the following LINQ statement:

    var q = from product in Product.All() 
      where product.ProductProperties.Where(property => 
        (property.ProductDefinitionField.Name == "MyProperty" && property.Value == "MyPropertyValue") 
        || (property.ProductDefinitionField.Name == "MyOtherProperty" && property.Value == "MyotherPropertyValue")).Count() == 2
        && product.ParentProductId == null
      select product;

     

     

Please Sign in or register to post replies

Write your reply to:

Draft