MVC 4. and Entity Framework Table Join
16,854
Solution 1
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,
{
}
Solution 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();
}
Related videos on Youtube
Author by
David Costelloe
Updated on May 25, 2022Comments
-
David Costelloe almost 2 years
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
-
mattytommo about 11 yearsIs it the same error using the second method?
-
David Costelloe about 11 yearsExcellent 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.
-
Forty-Two about 11 yearsRE: 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 about 11 yearsAlternatively, you may choose to create an un-mapped DTO, project to that, then map back to the ADPerson entity.
-
David Costelloe about 11 yearsOh 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 about 11 yearsThe first var query is failing is there suppose to be select new?
-
Forty-Two about 11 years@DavidCostelloe yes, sorry. Edited answer
-
eburgos about 11 yearsit might be you have a trailing comma at "MsdnSubscription = c.MsdnTypeDescription," and also on the second one
-
David Costelloe about 11 yearsThanks found it was the brace { and }; almost there :-)
-
David Costelloe about 11 yearsgetting error "Invalid column name 'MsdnSubscription" on display of field
-
David Costelloe about 11 yearsI should be returning query but it says I can't due to being anonymous.
-
David Costelloe about 11 yearsLooks 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?
-
Forty-Two about 11 years@david that's what the for each loop does. It converts each anonymous object returned by the query to an ADPerson entity. Just declare a new list of ADPerson and add each entity to the list as it is converted.
-
David Costelloe about 11 yearsthanks once you mentioned that it became obvious on what was needed. Finally works the way it should. Updated as answered and complete. I spend way to much trying to come up with a complex way when I should have been looking to the simple way :-(