CODEWITHSUNDEEP
c#sqlasp.netlinqmodel-view-controller
Mohamad Ghanem
Mohamad Ghanem

Reputation: 599

Convert the result of LINQ query to list of Custom Model

Good evening everyone.

I have a LINQ to SQL query result, and I want to convert this result to a list of Custom Model class

down here the Class Model:

public class Beneficiary
    {
        public int BeneficiaryId { get; set; }

        [Required]
        public string FirstName { get; set; }
        [Required]
        public string LastName { get; set; }

        public DateTime? CreatedAt { get; set; }

        [Column(TypeName = "date")]
        public DateTime? BithDate { get; set; }

        public int? CommunityId { get; set; }
        public virtual Community Community { get; set; }

        [Required]
        public string Gender { get; set; }


        public string IdNumber { get; set; }

        [Required]
        [StringLength(50)]
        public string AppName { get; set; }

        [StringLength(50)]
        public string BeneficiaryNumber { get; set; }

        public int CompanyId { get; set; }
        public virtual Company Company { get; set; }

        public virtual ICollection<BeneficiaryProject> BeneficiaryProjects { get; set; }

        public virtual ICollection<Card> Cards { get; set; }

        public virtual ICollection<Invoice> Invoices { get; set; }
    }

and here is the query that I used :

var list  = (from B in db.Beneficiaries
                               join ben in db.BeneficiaryProjects on B.BeneficiaryId equals ben.BeneficiaryId
                               where (ben.CardNeeded == true && ben.ProjectId == temp.ProjectId)
                               select new Beneficiary()
                               {
                                   BeneficiaryId = B.BeneficiaryId,
                                   FirstName = B.FirstName,
                                   LastName = B.LastName,
                                   IdNumber = B.IdNumber,
                                   Gender = B.Gender
                               });

the above query is successfully executed, but when I convert the var list to a list of Beneficiary as following:

            List<Beneficiary> list1 = list.ToList<Beneficiary>();

but I got the below exception :

 An exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll but was not handled in user code

Additional information: The entity or complex type 'E_Voucher.Models.Beneficiary' cannot be constructed in a LINQ to Entities query.

Upvotes: 1

Views: 11384

Answers (3)

DaniDev
DaniDev

Reputation: 2631

The reason why you are getting this error (in this line:)

List<Beneficiary> list1 = list.ToList<Beneficiary>();

is that you are trying to convert a Linq Query variable to a List.

All you really need to do is the following:

List<Beneficiary> list1 = list.ToList();

the query you have for temp.ProjectID:

var projects = db.Projects.Where(p => p.ContractId == id); foreach (var temp in projects)

should be something like:

int ProjectId = (from P in db.Projects where P.ContractId == id select P.ProjectId).FirstOrDefault();

and then take that value and put into your next query:

... where (ben.CardNeeded == true && ben.ProjectId == ProjectId)

Upvotes: 2

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726709

Make conversion to custom class after retrieving the results from EF:

var list  = (
    from B in db.Beneficiaries
    join ben in db.BeneficiaryProjects on B.BeneficiaryId equals ben.BeneficiaryId
    where (ben.CardNeeded == true && ben.ProjectId == temp.ProjectId)
    select new {
        B.BeneficiaryId,
        B.FirstName,
        B.LastName,
        B.IdNumber,
        B.Gender
    }
).AsEnumerable()
.Select(B => new Beneficiary() {
    BeneficiaryId = B.BeneficiaryId,
    FirstName = B.FirstName,
    LastName = B.LastName,
    IdNumber = B.IdNumber,
    Gender = B.Gender
}).ToList();

Note: The reason you get the error on running ToList is that EF queries defer execution. Your query may be incorrect, but the only way to find out is to try getting its results.

Upvotes: 3

RizJa
RizJa

Reputation: 2031

I believe you can't project your object of type Beneficiary from within LINQ to Entities to a mapped entity object.

Try this:

db.Beneficiaries.Join(db.BeneficiaryProjects, B => B.BeneficiaryId, ben => ben.BeneficiaryId, (B, ben) => new { B, ben })
                .Where(x => x.ben.CardNeeded == true && x.ben.ProjectId == temp.ProjectId)
                .Select(x => x.B)
                .ToList();

It's a lamda expression that should do the same thing as your LINQ query but will select your Beneficiary model object and map it. The ToList() can be replaced with an AsQueryable() or AsEnumerable() based on your need.

Upvotes: 0

Related Questions