How to map an extra field from ExecuteQuery() in LINQ-TO-SQL but retain the LINQ entity

Question

Lets say I have a Position table and a related Team table I need to get a list of Position entities, plus the Team name from a related table, out of an ExecuteQuery() call. As in:

var list = dc.ExecuteQuery<T>("SELECT Position.*, Team.Name  AS TeamName " +
"FROM Position LEFT JOIN Team ON Position.TeamID=Team.TeamID");

(I need to use ExecuteQuery(), because the actual query is very complex.)

Yes, I could create a new flat class with all of the fields from the Position class plus the teamname field, but I need the result set to be actual LINQ entities, not just POCOs, because I will iterate over those records and update some fields.

First idea, Create a new class that contains the Position and the new field

public class PositionExtended
{
   public Position position {get; set;}
   public string teamname {get; set;}
}

ExecuteQuery<PositionExtended>("SELECT Position.*, Team.Name  AS TeamName " +
"FROM Position LEFT JOIN Team ON Position.TeamID=Team.TeamID");

This correctly maps the teamname, but not the position object.

---

Second Idea, inherit from the Position class:

public class PositionExtended : Position
{
   public string teamname {get; set;}
}

ExecuteQuery<PositionExtended>("SELECT Position.*, Team.Name  AS TeamName " +
"FROM Position LEFT JOIN Team ON Position.TeamID=Team.TeamID");

This returns an error "the field (first field of the Position class) is not part of the mapping for type PositionExtended. Is the member above the root of an inheritance hierarchy?"

---

Last idea, Use a partial class:

public partial class Position
{
   [Column(Name="TeamName")]
   public  string TeamName {get; set;}
}

ExecuteQuery<Position>("SELECT Position.*, Team.Name  AS TeamName " +
"FROM Position LEFT JOIN Team ON Position.TeamID=Team.TeamID");

This actually works for this specific SQL query, but it changes the Position class, and all other LINQ calls returning Positions fail, because teamname field is not really part of the Position table, thus not returned in the query.

Does anyone have a workaround for any of these ideas, or a better idea?

Answer 1

I have not tried this yet, but I am facing a very similar challenge. I am considering using the Attach method of the Linq.Table object to manually set up true entity instances created from data returned in a list of POCOs.

Answer 2

Better idea:

Use Dapper for these cases :)

Another idea:

Add a pseudo property.

IOW, add the TeamName property to the Position class. If it is null, it is not being used. Also do not decorate it with LINQ2SQL attributes.

Yet another idea:

If you dont care about mutating the objects, just create a view and drop that into the designer. Add some extra properties to resolve to real entities, if needed.