none
Optional contains or any like a where in clause RRS feed

  • Question

  • I have a list of people which may have 1 or more attributes. In the query I need to use an "any" I think to check what attributes a person may have assigned to them.

    I want to add the where clause based on if someone selected some attributes in a list on the form. 

     

    //get all attributes
    
           var attributeQuery =
                        from attribute in db.Attributes
                        select attribute.AttributeID;
    
                    List<int> allAttributes = new List<int>();
                    allAttributes.AddRange(attributeQuery);  
    
    
    
    
    var query =
                        from person in db.Persons
                     select person;
    
    
    //I need to do something like this. The Any returns a bool which is wrong but I hope you get the idea
    
    
                    if (s.AttributeList.Count > 0)
                    {
                        query = query.Where(allAttributes.Any(q => q == personattribute.AttributeID));
                    }
    

    Tuesday, October 11, 2011 6:46 PM

Answers

  • I couldn't figure this out so I worked it out a different way.

     

          var query =
    
                        from persons in db.Persons
                        select persons;
    
                    if(s.AttributeList.Count > 0)
                    {
                        query = from persons in query
                            join personattributes in db.PersonAttributes on persons.PersonID equals personattributes.PersonID
                            join attributes in db.Attributes on personattributes.AttributeID equals attributes.AttributeID
                            where s.AttributeList.Contains(attributes.AttributeID)
                            select persons;
                    }

    • Marked as answer by forwheeler Tuesday, October 18, 2011 6:07 PM
    Tuesday, October 18, 2011 6:07 PM

All replies

  • Hi.

    I assume you have another table PersonAttribute that bind the Person and Attribute tables in Many-to-Many relationship.

    This table is a full join table, and it is not represented in the EDM. But you can navigate from Person to Attribute tables via Person.Attributes navigation property. Here is your Where clause then:

    query = query.Where(p => p.Attributes.Any());

     

    Regards

    Tuesday, October 11, 2011 8:07 PM
  • Yes this is a Many to Many relationship with this join table.
    I am manually creating the left join but maybe there is an easier way.
              
    var query =
                        from person in db.Persons
                     select person;
    
              join personattribute in context.PersonAttributes on Person.PersonID equals PersonAttribute.PersonID
                        into attributes
                        from PersonAttribute in attributes.DefaultIfEmpty()
    
    Your suggestion doesn't work since I can't get to the attribute table like that.
    I have a helper class to allow me to conbine properties of the Person entity and the Attribute entity.
    In that class I have a list where I add attributes to the person from the form.
     
            public List<int> AttributeList = new List<int>();
    

    So I tried
     query = query.Where(p => p.AttributeList.Any());
    
    and I get the error:
    The specified type member 'AttributeList' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.
     

     
     
    Tuesday, October 11, 2011 8:54 PM
  • Hi forewheeler,

    Try this:

    var results= from a in db.Attributes
                              from p in a.Persons
                              where p.Name = "john"
                              select a;

    I believe based on how you have the relationship setup, this will give you the attributes for a person.


    Tom Overton
    Wednesday, October 12, 2011 12:18 AM
  • Hi forwheeler,

    Welcome!

    Would you please give us your datatables' structure, Is there any navigations between the two manytomany entities?

    >>I have a helper class to allow me to conbine properties of the Person entity and the Attribute entity.

    >>In that class I have a list where I add attributes to the person from the form.
    >>query = query.Where(p => p.AttributeList.Any());
    Does "p" have the AttributeList property?
    Have a nice day.

    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, October 12, 2011 6:28 AM
    Moderator
  • Hi forwheeler,

    Welcome!

    Would you please give us your datatables' structure, Is there any navigations between the two manytomany entities?

    >>I have a helper class to allow me to conbine properties of the Person entity and the Attribute entity.

    >>In that class I have a list where I add attributes to the person from the form.
    >>query = query.Where(p => p.AttributeList.Any());
    Does "p" have the AttributeList property?
    Have a nice day.

    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    I have a person table with a PersonID as the key, an Attribute Table with AttributeID as the key and an PersonAttribute table with the fields, PersonID and AttributeID along with its own key. There are relations between the PersonAttribute and the other two tables.

    The AttributeList is in the PersonWithCase helper class. List<int>AttributeList = new List<int>();

    This PersonWithCase is a helper class which has properties from 3 tables so I can pass that class between tiers.

    I fill the AttributeList with AttributeIDs from a listbox on the search form and then want to pass this to the LINQ query to determine if the person searched for has any of the selected attributes.

    All these tables are in the edmx file.

     

    internal List<PersonWithCase> SearchPersons(PersonWithCase p)
            {
                try
                {
                    Context db = new Context();
    
                  
                    var query =
                        from casePerson in db.CasePersons
                        join Personattribute in db.PersonAttributes on casePerson.Person.PersonID equals Personattribute.PersonID
                        into attributes
                        from Personattribute in attributes.DefaultIfEmpty()
                        
                     
                        select new PersonWithCase
                        {
                            PersonID = casePerson.PersonID,
                            LName = casePerson.Person.LName,
                            FName = casePerson.Person.FName,
                            AttributeDescription = Personattribute.Attribute.Description
                            
                        };
                    
    
                    if (!string.IsNullOrEmpty(s.LName))
                    {
                        query = query.Where(q => q.LName.Contains(s.LName));
                    }
                    if (!string.IsNullOrEmpty(s.FName))
                    {
                        query = query.Where(q => q.FName.Contains(s.FName));
                    }
                  
                    if (s.AttributeList.Count > 0)
                    {
                        //query = query.Where(p => p.Attribute.Any());
                            
                    }
                    return query.ToList<PersonWithCase>();
                }
                catch (Exception ex)
                {
                    logger.ErrorException("Application Error", ex);
                    return null;
                }
            }
    

     

     

    Wednesday, October 12, 2011 12:56 PM
  • I guess I haven't been clear so I will try to reword this.

     

    I have a person table with a PersonID as the key, an Attribute Table with AttributeID as the key and an PersonAttribute table with the fields, PersonID and AttributeID along with its own key. There are relations between the PersonAttribute and the other two tables.

    I have a form to search for the person and it has a listbox where you can specify the attributes of a person.

    If one or more attributes are selected then I need to add those to the LINQ to Entity query.

    Thursday, October 13, 2011 5:05 PM
  • Hi.

    Try this one:

    List<int> attrs = new List<int>(){1, 2}; // selected attributes Ids from the search form
    
    using (var db = new ForumContext())
    {
    	var people = db.People
    		.Where(p => p.Attributes.Any(a => attrs.Contains(a.AttributeId)));
    
    	foreach (Person p in people)
    	{
    		Console.WriteLine(p.Name);
    	}
    }
    
    

    Regards

    Thursday, October 13, 2011 5:37 PM
  • Hi.

    Try this one:

     

    List<int> attrs = new List<int>(){1, 2}; // selected attributes Ids from the search form
    
    using (var db = new ForumContext())
    {
    	var people = db.People
    		.Where(p => p.Attributes.Any(a => attrs.Contains(a.AttributeId)));
    
    	foreach (Person p in people)
    	{
    		Console.WriteLine(p.Name);
    	}
    }
    
    

    Regards

     


    That looks nice but I can't access the Attribute table like that since there is a many to many link table in between them.
    Thursday, October 13, 2011 7:53 PM
  • Any more ideas?
    Monday, October 17, 2011 5:04 PM
  • I couldn't figure this out so I worked it out a different way.

     

          var query =
    
                        from persons in db.Persons
                        select persons;
    
                    if(s.AttributeList.Count > 0)
                    {
                        query = from persons in query
                            join personattributes in db.PersonAttributes on persons.PersonID equals personattributes.PersonID
                            join attributes in db.Attributes on personattributes.AttributeID equals attributes.AttributeID
                            where s.AttributeList.Contains(attributes.AttributeID)
                            select persons;
                    }

    • Marked as answer by forwheeler Tuesday, October 18, 2011 6:07 PM
    Tuesday, October 18, 2011 6:07 PM