CODEWITHSUNDEEP
c#linqentity-frameworklistiqueryable
Rami Sakr
Rami Sakr

Reputation: 63

List Inside IQueryable Object

Given the following:

public class Person
{
  public int ID { get; set;}
  public string Name { get; set;}
  public IQueryable<Pet> Pets { get;set; }
}

public class Pet
{
  public int id { get; set; }
  public int OwnerId { get; set; }
  public string Name { get; set; }
}

public class SearchCriteria
{
  public string PersonName {get; set; }
  public List<string> PetNames {get; set;}
}

Implementing a select of all Persons with their pets while searching with an IQueryable

public List<Person> GetWithPets(SearchCriteria search)
{
     var people = (from p in context.People
                   where p.Name == search.PersonName
                   select new Person{
                          ID = p.ID,
                        Name = p.Name,
                        Pets = (from pt in context.Pets
                                where pt.OwnerId == p.ID
                                select new Pet {
                                   id = pt.ID,
                                 OwnerId = pt.OwnerId,
                                 Name = pt.Name
                                }).AsQueryable
                   }).AsQueryable();

       foreach(var str in search.PetNames)
       {
            people = people.Where(o=>o.Pets.Any(p=>p.Name == str));
       }
  return people.ToList();
}

My problem is that regardless of the foreach that searches the name, in the list of people that returns, pets is null even though there are pets associated with that person, where did I go wrong ?

EDIT:

public class Person
{
    public int ID { get; set; }
    public string Name { get; set; }
    public IQueryable<Animal> Pets { get; set; }
}

public class Animal
{
    public int id { get; set; }
    public int? OwnerId { get; set; }
    public string Name { get; set; }
}

public class SearchCriteria
{
    public string PersonName { get; set; }
    public List<string> PetNames { get; set; }
}



class Program
{
    public static List<Person> GetWithPets(SearchCriteria search)
    {
        using (DatabaseEntities context = new DatabaseEntities())
        {
            var people = (from p in context.Peoples
                          where p.Name == search.PersonName
                          select new Person
                          {
                              ID = p.ID,
                              Name = p.Name,
                              Pets = (from pt in context.Pets
                                      where pt.OwnerID == p.ID
                                      select new Animal
                                      {
                                          id = pt.ID,
                                          OwnerId = pt.OwnerID,
                                          Name = pt.Name
                                      }).AsQueryable()
                          }).AsQueryable();

            foreach (var str in search.PetNames)
            {
                people = people.Where(o => o.Pets.Any(p => p.Name == str));
            }
            return people.ToList();
        }
    }

Upvotes: 4

Views: 4953

Answers (4)

Slauma
Slauma

Reputation: 177163

If Person and Pet are entities of your model and if Person.Pets is a navigation property to the Pet entity and if you want to have the full Person entity with all the full Pet entities and refering to your comment...

my method is supposed to return a list of people that have name equals search.PersonName & ALL their pets but only those people who own the pets with those names in the search.PetNames

...you could use this:

public List<Person> GetWithPets(SearchCriteria search)
{
    var people = from p in context.People.Include("Pets")
                 where p.Name == search.PersonName
                    && p.Pets.Any(pt => search.PetNames.Contains(pt.Name))
                 select p;

    return people.ToList();
}

Upvotes: 2

RePierre
RePierre

Reputation: 9576

Add AsQueryable() to Pets collection:

Pets = (from pt in context.Pets  
        where pt.OwnerId == p.ID  
        select new Pet {  
            id = pt.ID,  
            OwnerId = pt.OwnerId,  
            Name = pt.Name  
        }).AsQuerable()

Upvotes: 0

Jayanga
Jayanga

Reputation: 887

May you could try something like this

var people = (from p in context.People
                   where p.Name == search.PersonName
                   select new Person{
                          ID = p.ID,
                        Name = p.Name,
                        Pets = (from pt in context.Pets
                                where pt.OwnerId == p.ID
                                select new Pet {
                                   id = pt.ID,
                                 OwnerId = pt.OwnerId,
                                 Name = pt.Name
                                })
                   }).Include("Pets").AsQueryable();

Upvotes: 0

Jayanga
Jayanga

Reputation: 887

Or you could try something like this

foreach(var str in search.PetNames)
       {
            people.Concat(people.Where(o=>o.Pets.Any(p=>p == str)).Include('Pet'));
       }

Upvotes: 0

Related Questions