Working With Linq Query in Mapping

Question

I have a class containing three Methods which are working perfectly :

1. To fetch the data from table :

       public IList<RetriveOperatorAcYo> GetRetriveOperatorListGrid(int Uid)
       {
           var persons = (from p in _Context.People
                         join a in _Context.Accounts on p.PersonId equals a.PersonId
                         join b in _Context.BusinessTypes on a.BusinessTypeId equals b.BusinessTypeId
                         join d in _Context.AccountUserRelations on a.AccountId equals d.AccountId
                         where b.Name == AccountBusinessTypes.Operator && a.IsDelete == false && d.Active == true && d.UserId == Uid
                         select p);
           return MapToRetriveOperatorYoList(persons);
       }
   

2. Use as middle function for separating list to single element :

       private IList<RetriveOperatorAcYo> MapToRetriveOperatorYoList(IEnumerable<EntityFramework.Person> persons)
       {
           return persons.Select(MapToRetirveOperatorYo).ToList();
       }
   

3. As a Mapping function to generate result :

       private RetriveOperatorAcYo MapToRetirveOperatorYo(EntityFramework.Person person)
       {
           var vendorYo = new RetriveOperatorAcYo
           {
               Id = person.PersonId,
               FirstName = person.FirstName,
               LastName = person.LastName,
               MobileNumber = person.MobileNumber,
               LandlineNumber = person.LandlineNumber
           }
   
           return vendorYo;
      }
   

Now I want to fetch only particular columns data in my first function.

I searched some techniques and tried this :

var persons = (from p in _Context.People
               join a in _Context.Accounts on p.PersonId equals a.PersonId
               join b in _Context.BusinessTypes on a.BusinessTypeId equals b.BusinessTypeId
               join d in _Context.AccountUserRelations on a.AccountId equals d.AccountId
               where b.Name == AccountBusinessTypes.Operator && a.IsDelete == false && d.Active == true && d.UserId == Uid
               select new 
               {
                  PersonId = p.PersonId,
                  FirstName = p.FirstName,
                  LastName = p.LastName,
                  MobileNumber = p.MobileNumber,
                  LandlineNumber = p.LandlineNumber
               });

But it giving me the error in the second function :

"The entity or complex type cannot be constructed in a LINQ to Entities query."

So is there any solution how to map the data and return the particular mapped class with particular details only.

Thanks for the Help in Advance.

Answer 1

You have not specified which class should be created with the LINQ query. Try

var persons = (from p in _Context.People
           join a in _Context.Accounts on p.PersonId equals a.PersonId
           join b in _Context.BusinessTypes on a.BusinessTypeId equals b.BusinessTypeId
           join d in _Context.AccountUserRelations on a.AccountId equals d.AccountId
           where b.Name == AccountBusinessTypes.Operator && a.IsDelete == false && d.Active == true && d.UserId == Uid
           select new RetriveOperatorAcYo()
           {
              PersonId = p.PersonId,
              FirstName = p.FirstName,
              LastName = p.LastName,
              MobileNumber = p.MobileNumber,
              LandlineNumber = p.LandlineNumber
           });

Answer 2

To maintain the pattern with the 3 methods and do the partial query you want, while not using your existing DTO in the first method, you will have to resort to passing around dynamic objects and change your method signatures. Something like this should work, although it's not recommended - note the use of dynamic in the 2nd and 3rd methods:

public IList<RetriveOperatorAcYo> GetRetriveOperatorListGrid(int Uid)
{
    var persons = (from p in _Context.People
        join a in _Context.Accounts on p.PersonId equals a.PersonId
        join b in _Context.BusinessTypes on a.BusinessTypeId equals b.BusinessTypeId
        join d in _Context.AccountUserRelations on a.AccountId equals d.AccountId
        where b.Name == AccountBusinessTypes.Operator && a.IsDelete == false && d.Active == true && d.UserId == Uid
        select new 
        {
            PersonId = p.PersonId,
            FirstName = p.FirstName,
            LastName = p.LastName,
            MobileNumber = p.MobileNumber,
            LandlineNumber = p.LandlineNumber
         });
    return MapToRetirveOperatorYo(persons);
}

private IList<RetriveOperatorAcYo> MapToRetriveOperatorYoList(IEnumerable<dynamic> persons)
{
    return persons.Select(MapToRetirveOperatorYo).ToList();
}

private RetriveOperatorAcYo MapToRetirveOperatorYo(dynamic person)
{
    var vendorYo = new RetriveOperatorAcYo
    {
        Id = person.PersonId,
        FirstName = person.FirstName,
        LastName = person.LastName,
        MobileNumber = person.MobileNumber,
        LandlineNumber = person.LandlineNumber
    }
    return vendorYo;

}

The better option would be to change your first method to select new RetriveOperatorAcYo, as mentioned in this answer.

Answer 3

The problem with this approach is that the EF cannot partially load an EF type with selected columns. If you load a type with partial information it doesn't know how to handle the rest of the columns that remain unfilled. What should it do in an update? In order to use these partial columns you can create another class to hold this information for you. But even then you have to decide what to do with the unselected columns in your MapToRetirveOperatorYo method. Here is to demonstrate:

public class PersonDTO
        {
            public long PersonID { get; set; }
            public string FirstName { get; set; }
            public string LastName { get; set; }
            public string MobileNumber { get; set; }
            public string LandlineNumber { get; set; }
        }

Now you can use this class when you want to create your anonymous class:

var persons = (from p in _Context.People
                join a in _Context.Accounts on p.PersonId equals a.PersonId
                join b in _Context.BusinessTypes on a.BusinessTypeId equals b.BusinessTypeId
                join d in _Context.AccountUserRelations on a.AccountId equals d.AccountId
                where b.Name == AccountBusinessTypes.Operator && a.IsDelete == false && d.Active == true && d.UserId == Uid
                select new PersonDTO()
                {
                    PersonId = p.PersonId,
                    FirstName = p.FirstName,
                    LastName = p.LastName,
                    MobileNumber = p.MobileNumber,
                    LandlineNumber = p.LandlineNumber
                }); 

In your other function:

private IList<RetriveOperatorAcYo> MapToRetriveOperatorYoList(IEnumerable<PersonDTO> persons)
{
        //here you have to decide what to do with Email, DOB and active as 
        //you are not selecting those in your Person DTO class ! Here is where EF would not know what to do.
        return person.Select(person => new RetriveOperatorAcYo
        {
            Id = person.PersonId,
            FirstName = person.FirstName,
            LastName = person.LastName,
            MobileNumber = person.MobileNumber,
            LandlineNumber = person.LandlineNumber,
            Email = person.Email, //Error
            DOB = person.DOB, //Error
            Active = person.Active //Error
        }.ToList()
 }

Answer 4

Having read all the discussion, and great suggestions, I think your answer is, No, there is not any solution how to map the data and return the particular mapped class with particular details only. I think you will have to call Microsoft and persuade them to change how EF should accept partial objects. This is the correct answer since you do not want to change existing code.