How cast System.Data.Entity.Infrastructure.DbQuery type to System.Collections.Generic.List type?

Question

I have two simple Lists:

public partial class Visitor
{
    public Visitor()
    {
        this.Visits = new HashSet<Visit>();
    }

    public int Id { get; set; }
    public int PermitId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string MiddleName { get; set; }
    public bool IsValid { get; set; }
    public System.DateTime RegistrationDate { get; set; }
    public byte[] Picture { get; set; }
    public virtual ICollection<Visit> Visits { get; set; }
}

public partial class Visit
{
    public int Id { get; set; }
    public int VisitType { get; set; }
    public System.DateTime VisitDate { get; set; }
    public Nullable<int> Visitor_Id { get; set; }

    public virtual Visitor Visitor { get; set; }
}

In WCF method i wrote query and tried to return result:

public List<Visitor> AllVisitors()
        {
            using (var te = new TurnstileDbEntities())
            {
                te.Configuration.ProxyCreationEnabled = false;


                return (List<Visitor>) te.Visitors.SelectMany(visitors => te.Visits
                    .Where(o => o.Visitor_Id == visitors.Id)
                    .DefaultIfEmpty(), (visitors, visit) => new {visitors, visit});
            }
        }

Well, as expected, I received an exception:

Additional information: Failed to cast the type of object "System.Data.Entity.Infrastructure.DbQuery1[<>f__AnonymousType02[TurnstileWcfService.Visitor,TurnstileWcfService.Visit]]" to type "System.Collections.Generic.List`1[TurnstileWcfService.Visitor]".

Ok. I rewrite this. Declare new class.

public class JoinResult
{
    public Visitor Visitors { get; set; }
    public Visit Visits { get; set; }
}

And rewrite method.

   public IQueryable AllVisitors()
    {
        using (var te = new TurnstileDbEntities())
        {
            te.Configuration.ProxyCreationEnabled = false;

            return te.Visitors.Join(te.Visits, 
                r => r.Id, a => a.Visitor_Id, (r, a) => new JoinResult
            {
                Visits = a,
                Visitors = r
            });
        }
    }

It is works but i want use Visitor class. Visitor class contain ICollectionVisits and i want populate it. How can I do this in Linq statement without declaration of new class?

Also I can get what i want in following code. But I don't like this.

var visitors = te.Visitors.ToList();
foreach (var item in visitors)
{
      item.Visits = te.Visits.Where(v => v.Visitor_Id == item.Id).ToList();
}

on the first glance [this](http://stackoverflow.com/questions/16734320/how-to-cast-an-anonymous-type-to-iqueryableorder-detail-in-c) might help — Mong Zhu, Jun 02 '16 at 15:03
Looks like you're missing the `Include` method: `te.Visitors.Include(v => v.Visits)`. — Gert Arnold, Jun 02 '16 at 17:50
@GertArnold: Visits is a virtual property. Shouldn't it be included by default? It was included when I ran my test. — poppertech, Jun 02 '16 at 17:52
I get exception when ProxyCreationEnabled = true because models generated from EF. I wrote about it yesterday http://stackoverflow.com/questions/37565418/failed-to-invoke-the-service-when-the-method-returns-listt — user6408649, Jun 02 '16 at 18:09
@GertArnold : I missed ProxyCreationEnabled = false. Another lesson learned. — poppertech, Jun 02 '16 at 18:13
With loop i get another exception - Circular Reference error when serializing objects. But that's different lesson too. — user6408649, Jun 02 '16 at 18:24
When serializing data, it's always better to disable lazy loading and to `Include` what you need to be included. As for the circular reference, you have to tell the serializer to ignore them: https://msdn.microsoft.com/en-us/library/hh241056%28v=vs.100%29.aspx. Better still is to use DTOs. — Gert Arnold, Jun 02 '16 at 18:29

poppertech · Accepted Answer · 2016-06-02T18:45:24.990

7

Gert Arnold is right, try:

List<Visitor> visitors = te.Visitors.Include(v => v.Visits).ToList();

Edit I got it to work as follows as well:

List<Visitor> visitors = te.Visitors.Include("Visits").ToList();

edited Jun 02 '16 at 18:45

answered Jun 02 '16 at 15:54

poppertech

1,286
2
9
17

Yes. Now it's great. But as I said above, another exception occured. Circular Reference error when serializing objects. Thank you. – user6408649 Jun 02 '16 at 19:23
Glad to hear this part works. Please mark it as an answer, and I will take a look at your other error. – poppertech Jun 02 '16 at 19:36

How cast System.Data.Entity.Infrastructure.DbQuery type to System.Collections.Generic.List type?

1 Answers1