How to convert Linq.IQueryable to Collections.List

Question

_{Wednesday, June 5, 2013 7:19 PM}

hi all,

i'm having a slight problem that i cannot find a solution on google. I'm doing MVC and I am doing a join. Here's a snip of my code:

// class member model
public class Member
{
[Required]
public int MemberID { get; set; }

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

[Required]
public int CompanyID { get; set; }

[NoMapped]
public string CompanyName { get; set; }
}

// class company model
public class Company
{
[Required]
public int CompanyID { get; set; }

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

Here's the index() function from my MemberController

public ViewResult Index()
{
var query = from m in db.Members
join c in db.Companies on m.CompanyID equals c.CompanyID
select new Member()
{
MemberID = m.MemberID,
MemberName = m.MemberName,
CompanyID = m.CompanyID,
CompanyName = c.CompanyName
}

return View(query.ToList());

}

Error: The entity or complex type 'ProjectName.Models.Member' cannot be constructed in LINQ to Entities query.

I see that the type is different: When i do this: db.Member.ToList() then I get System.Collections.List<Member>; but with the code above i get System.Linq.IQueryable<Member>; I think it has to return System.Collections.Generic.List<Member>.

I'm new to this linq stuff. Any help is appreciated. Thanks.

All replies (16)

_{Wednesday, June 5, 2013 7:40 PM}

Try this:

public ViewResult Index()
{
    var query = from m in db.Members
               join c in db.Companies on m.CompanyID equals c.CompanyID
               select new {m,c};
    var list = new List<Member>();
    foreach(var item in query)
    {
        list.Add(new Member()
                {
                    MemberID=item.m.MemberID,
                    MemberName=item.m.MemberName,
                    CompanyID=item.m.CompanyID,
                    CompanyName=item.c.CompanyName
                });
    }
   return View(list);
}

_{Wednesday, June 5, 2013 7:40 PM}

I believe you can cast to Member in the ToList call:

return View(query.ToList<Member>());

_{Wednesday, June 5, 2013 7:54 PM}

Wang, i had something similar to that before. but i don't like the idea of going through the foreach to do it.

_{Wednesday, June 5, 2013 8:06 PM}

var query = from m in db.Members
            from c in db.Companies
            where m.CompanyID == c.CompanyID
            select new Member()
            {
                MemberID = m.MemberID,
                MemberName = m.MemberName,
                CompanyID = m.CompanyID,
                CompanyName = c.CompanyName
            }

return View(query.ToList());

_{Wednesday, June 5, 2013 8:12 PM}

And i thought that's the solution =)

AceCorban, that doesn't work. It gives me the same error

_{Wednesday, June 5, 2013 8:16 PM}

EvilScott .. =) nope. same error.

question though,

if you do
from m in db.Members
from c in db.Companies

will it performs faster than the join?

_{Wednesday, June 5, 2013 8:59 PM}

Wang, i had something similar to that before. but i don't like the idea of going through the foreach to do it.

Then you probably can pass a anonymous type to your view:

public ViewResult Index()
{
    var query = from m in db.Members
               join c in db.Companies on m.CompanyID equals c.CompanyID
               select new {m.MemberID,m.MemberName,m.CompanyID,c.CompanyName};
    return View(query.ToList());
}

In your view your model should be of type List<dynamic>.

_{Thursday, June 6, 2013 5:21 PM}

using anonymous type doesn't work. i guess there's no other solution than using the for loop that Wang suggested. however, i think there has to be a better way =)

_{Thursday, June 6, 2013 7:12 PM}

After doing some more research, the problem is that the Entity framework does not actually allow you to project onto mapped entities, because the context you are using will not be able to track or maintain data integrity. If you do wish to do it though, it appears you might be able to get around it by converting it to a list collection first then populating the entity type after the fact. Try this:

var query = (from m in db.Members
            join c in db.Companies on m.CompanyID equals c.CompanyID
            select m).ToList();
     
var results = from m in query
              select new Member()
              {
                  MemberID = m.MemberID,
                  MemberName = m.MemberName,
                  CompanyID = m.CompanyID,
                  CompanyName = c.CompanyName
              };

return View(results.ToList());

_{Thursday, June 6, 2013 8:31 PM}

Thanks EvilScott.

I have not plug your code in and give it a try, I think it would work; however, it seems like two select statements against the db. It's not wrong, but performance wise it's not fast (i think).

I'll give it a try later tonight. If there's no way, I think Wang's solution is less heavy on the db.

_{Thursday, June 6, 2013 8:38 PM}

It's not two select statements against the database. The first query gets the data from the database itself and immediately uses .ToList() to dump it into an instance not bound to the datacontext it was pulled from. Then the second statement is reading from the already populated info and creating the members.

_{Sunday, June 9, 2013 6:45 PM}

Evil, you're right about that and i think your solution is the best answer.

_{Wednesday, June 26, 2013 10:23 AM}

EvilScott, today I get a chance to use the your suggested solution; however, in the second query the compiler complains that c does not exist in the current context.

_{Wednesday, June 26, 2013 11:18 AM}

thats because the list only has the mebers data, no company data. create an anonymous object to hold both.

var query = (from m in db.Members
            join c in db.Companies on m.CompanyID equals c.CompanyID
            select new {m=m, c=c}).ToList();
     
var results = from q in query
              select new Member()
              {
                  MemberID = q.m.MemberID,
                  MemberName = q.m.MemberName,
                  CompanyID = q.m.CompanyID,
                  CompanyName = q.c.CompanyName
              };

return View(results.ToList());

but this is much simpler looking as:

var results = (from m in db.Members
            join c in db.Companies on m.CompanyID equals c.CompanyID
            select new {m=m, c=c}).ToList().Select(
            {
                  MemberID = q.m.MemberID,
                  MemberName = q.m.MemberName,
                  CompanyID = q.m.CompanyID,
                  CompanyName = q.c.CompanyName
            });

return View(results.ToList());

_{Thursday, July 4, 2013 8:58 PM}

When you need to return an IQueryable from a method the ToList() options does not exist (not matter what your opinion on using this way is).

The only way to evaluate dynamic projections appears to be enumeration - ie. the for(var item in query) approach.

For those looking for a more concise syntax you may prefer this:

IList<dynamic> results = Enumerable.Cast<object>(query).Cast<dynamic>().ToList();

_{Monday, September 16, 2013 2:52 AM}

The only problem here were two braces.

public ViewResult Index()

{

var query = from m in db.Members

join c in db.Companies on m.CompanyID equals c.CompanyID

select new Member

{

MemberID = m.MemberID,

MemberName = m.MemberName,

CompanyID = m.CompanyID,

CompanyName = c.CompanyName

}

return View(query.ToList());

}

Please let me know whether it works?

Last updated on 2013-06-05

Share via

How to convert Linq.IQueryable to Collections.List

Question

All replies (16)

Additional resources