MVC 4. and Entity Framework Table Join

Question

This item is driving me mad ;-) I am trying to do a simple query joining two tables

I have the following:

Repository Class method

 public IQueryable<ADPerson> FindAll(string UserId)
    {
        return (from p in db.ADPerson
                select p); 


    }

In my Controller:

  var ADPersonList = from o in ADPersonDB.FindAll(GetUserId())
                    join c in MSDNTypeDB.FindAll(GetUserId()) on o.MsdnTypeId equals c.MsdnTypeId
                    select new ADPerson()
                    {

                        AdPersonId = o.AdPersonId,
                        SamAccountName = o.SamAccountName,
                        Description = o.Description,
                        DisplayName = o.DisplayName,
                        UserPrincipalName = o.UserPrincipalName,
                        Enabled = o.Enabled,
                        LastUpdated = o.LastUpdated,
                        OnlineAssetTag = o.OnlineAssetTag,
                        MsdnTypeId = o.MsdnTypeId,
                        MsdnSubscription = c.MsdnTypeDescription,


                    };

I keep getting an error:

{"The specified LINQ expression contains references to queries that are associated with different contexts."}

I also tried adding to the repository class:

Repository Class method

 public IQueryable<ADPerson> FindAll(string UserId)
    {
        //return (from p in db.ADPerson
        //        select p); 

        var query = from o in db.ADPerson
                    join c in db.MsdnTypes on o.MsdnTypeId equals c.MsdnTypeId
                    select new ADPerson()
                    {

                        AdPersonId = o.AdPersonId,
                        SamAccountName = o.SamAccountName,
                        Description = o.Description,
                        DisplayName = o.DisplayName,
                        UserPrincipalName = o.UserPrincipalName,
                        Enabled = o.Enabled,
                        LastUpdated = o.LastUpdated,
                        OnlineAssetTag = o.OnlineAssetTag,
                        MsdnTypeId = o.MsdnTypeId,

                        MsdnSubscription = c.MsdnTypeDescription,


                    };

        return query;

    }

is it really so hard to do a simple join between two tables and populate the variable in Entity framework

Thanks

Excellent point: No the error returned on the second is: The entity or complex type 'project.Models.ADPerson' cannot be constructed in a LINQ to Entities query. — David Costelloe
– David Costelloe, Commented Mar 14, 2013 at 18:06
RE: the second error. That's because you cannot project onto a mapped entity. You may project the query to an anonymous object, and then map it to the ADPerson entity afterwards — Forty-Two
– Forty-Two, Commented Mar 14, 2013 at 18:09
Alternatively, you may choose to create an un-mapped DTO, project to that, then map back to the ADPerson entity. — Forty-Two
– Forty-Two, Commented Mar 14, 2013 at 18:10
Oh so return as List rather than IQuerable? Sorry little confused as both tables are mapped in repository classes..ok not sure how to create an un-mapped DTO and map back again — David Costelloe
– David Costelloe, Commented Mar 14, 2013 at 18:15

Forty-Two · Accepted Answer · 2013-03-14 19:52:32Z

6

Project to an anonymous object

var query = from o in db.ADPerson
   join c in db.MsdnTypes on o.MsdnTypeId equals c.MsdnTypeId
   select new 
   {
     AdPersonId        = o.AdPersonId,
     SamAccountName    = o.SamAccountName,
     Description       = o.Description,
     DisplayName       = o.DisplayName,
     UserPrincipalName = o.UserPrincipalName,
     Enabled           = o.Enabled,
     LastUpdated       = o.LastUpdated,
     OnlineAssetTag    = o.OnlineAssetTag,
     MsdnTypeId        = o.MsdnTypeId,
     MsdnSubscription  = c.MsdnTypeDescription,
  };

Then map back to your entity

foreach (var item in query)
{
  var adPerson = new ADPerson
  {
    AdPersonId         = item.AdPersonId,
    SamAccountName     = item.SamAccountName,
    Description        = item.Description,
    DisplayName        = item.DisplayName,
    UserPrincipalName  = item.UserPrincipalName,
    Enabled            = item.Enabled,
    LastUpdated        = item.LastUpdated,
    OnlineAssetTag     = item.OnlineAssetTag,
    MsdnTypeId         = item.MsdnTypeId,
    MsdnSubscription   = item.MsdnTypeDescription,
  {
}

edited Mar 14, 2013 at 19:52

answered Mar 14, 2013 at 18:22

Forty-Two

7,6252 gold badges39 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

David Costelloe Over a year ago

The first var query is failing is there suppose to be select new?

Forty-Two Over a year ago

@DavidCostelloe yes, sorry. Edited answer

David Costelloe Over a year ago

getting error "Invalid column name 'MsdnSubscription" on display of field

David Costelloe Over a year ago

I should be returning query but it says I can't due to being anonymous.

David Costelloe Over a year ago

Looks like I need to convert the anonymous to Iquerable<ADPerson> any ideas on how i can do that. Does seem like an aweful lot of trouble to join a table in the Entity framework there must be an easier way?

|

6 revs · Accepted Answer · 2013-03-14 23:16:33Z

2

   public IQueryable<ADPerson> FindAll(string UserId)
    {
       // return (from p in db.ADPerson
       //        select p);
        List<ADPerson> lst = new List<ADPerson>();
        var query = from o in db.ADPerson
                    join c in db.MsdnTypes on o.MsdnTypeId equals c.MsdnTypeId
                    select new
                         {

                             AdPersonId = o.AdPersonId,
                             SamAccountName = o.SamAccountName,
                             Description = o.Description,
                             DisplayName = o.DisplayName,
                             UserPrincipalName = o.UserPrincipalName,
                             Enabled = o.Enabled,
                             LastUpdated = o.LastUpdated,
                             OnlineAssetTag = o.OnlineAssetTag,
                             MsdnTypeId = o.MsdnTypeId,
                             MsdnSubscription = c.MsdnTypeDescription

                         };
        foreach (var item in query)
        {
            var adPerson = new ADPerson()
              {
                  AdPersonId = item.AdPersonId,
                  SamAccountName = item.SamAccountName,
                  Description = item.Description,
                  DisplayName = item.DisplayName,
                  UserPrincipalName = item.UserPrincipalName,
                  Enabled = item.Enabled,
                  LastUpdated = item.LastUpdated,
                  OnlineAssetTag = item.OnlineAssetTag,
                  MsdnTypeId = item.MsdnTypeId,
                  MsdnSubscription = item.MsdnSubscription
              };
            lst.Add(adPerson);

        }

        return lst.AsQueryable();



    }

edited Mar 14, 2013 at 23:16

community wiki

6 revs
David Costelloe

2 Comments

eburgos Over a year ago

it might be you have a trailing comma at "MsdnSubscription = c.MsdnTypeDescription," and also on the second one

David Costelloe Over a year ago

Thanks found it was the brace { and }; almost there :-)

Collectives™ on Stack Overflow

MVC 4. and Entity Framework Table Join

2 Answers 2

7 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related