linq to entities does not recognize the method System.Collections.Generic.List

entity-framework linq

Question

I have below entities :

public class Person
{
    #region Primitive Properties
    public virtual int PersonId {get; set;}
    public virtual string FirstName{get; set;}
    public virtual string LastName { get; set; }
    #endregion

    #region Navigation Projperties
    public virtual ICollection<Address> Addresses { get; set; }
    #endregion
}

public class Address
{
    #region primitive properties
    public virtual int AddressId
    {
        get;
        set;
    }
    public virtual int PersonId
    {
        get;
        set;
    }
    public virtual int AddressSubTypeId
    {
        get;
        set;
    }
    public virtual string CompleteAddress
    {
        get;
        set;
    }
    public virtual bool IsActive
    {
        get;
        set;
    }
    #endregion

}

and have below models :

public class Person
{
    public int PersonId { get; set; }
    public string Name { get; set; }
    public string LastName { get; set; }
    public List<Email> Emailes { get; set; }
    public List<Phone> Phones { get; set; }
}

public class Phone
{
    public int PhoneId { get; set; }
    public string Name { get; set; }
}


public class Email
{
    public int EmailId { get; set; }
    public string Name { get; set; }
}

I want to get list of my person with below code :

 return context.Persons.Select(x => new Model.Person
                        {
                            PersonId = x.PersonId,
                            Name = x.FirstName,
                            LastName = x.LastName,
                            Phones = x.Addresses.Where(a => a.AddressSubTypeId == 1).Select(a => new Model.Phone
                                                {
                                                    PhoneId = a.AddressId,
                                                    Name = a.CompleteAddress
                                                }).ToList(),
                            Emailes = x.Addresses.Where(a => a.AddressSubTypeId == 2).Select(a => new Model.Email
                                                {
                                                    EmailId = a.AddressId,
                                                    Name = a.CompleteAddress
                                                }).ToList()

                        }).ToList();

When I run above expression raised below error :

LINQ to Entities does not recognize the method 'System.Collections.Generic.List1[Model.Phone] ToList[Phone](System.Collections.Generic.IEnumerable1[Model.Phone])' method, and this method cannot be translated into a store expression.

1
2
10/31/2013 9:59:32 AM

Accepted Answer

What you have written will try to translate

x.Addresses.Where(a => a.AddressSubTypeId == 1).Select(a => new Model.Phone
{
    PhoneId = a.AddressId,
    Name = a.CompleteAddress
}).ToList()

and

Emailes = x.Addresses.Where(a => a.AddressSubTypeId == 2).Select(a => new Model.Email
{
    EmailId = a.AddressId,
    Name = a.CompleteAddress
}).ToList()

in an SQL statement and it fails.

You should first get Persons in memory and then apply the above select. You can fetch Persons by calling ToList() or AsEnumerable()... These calls will materialize the query and then it will be possible to apply the lists.

return context.Persons.ToList().Select(x => new Model.Person
    {
        PersonId = x.PersonId,
        Name = x.FirstName,
        LastName = x.LastName,
        Phones = x.Addresses.Where(a => a.AddressSubTypeId == 1).Select(a => new Model.Phone
            {
                PhoneId = a.AddressId,
                Name = a.CompleteAddress
            }).ToList(),
        Emailes = x.Addresses.Where(a => a.AddressSubTypeId == 2).Select(a => new Model.Email
            {
                EmailId = a.AddressId,
                Name = a.CompleteAddress
            }).ToList()

    }).ToList();

Keep in mind that calling ToList(), will get all records in memory, which may cause heavy utilization of the server's memory. In your case you would anyway select all rows, so this might not be an issue, but you should consider adding a Where clause and even a Select for plain fields, before calling ToList() in order to get exactly what you need, and not the whole table.

4
10/31/2013 10:11:04 AM

Popular Answer

You cannot call methods inside the expression, materialize the queries you are referring first and then use the materialized lists in the queries.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow