Is there a quick way to get every association between two entities?

Question

I have two tables in my database: TPM_AREAS and TPM_WORKGROUPS. There exists a many-to-many relationship between these two tables, and these relationships are stored in a table called TPM_AREAWORKGROUPS. This table looks like this:

What I need to do is load all these mappings into memory at once, in the quickest way possible. As TPM_AREAWORKGROUPS is an association, I can't just say:

var foo = (from aw in context.TPM_AREAWORKGROUPS select aw);

I can think of three ways to possibly do this, however I'm not quite sure how to accomplish each of them nor which one is the best.

1) Load in every workgroup, including the associated areas:

Something like:

var allWG = (from w in context.TPM_WORKGROUPS.Include("TPM_AREAS")
             where w.TPM_AREAS.Count > 0
             select w);
// Loop through this enumeration and manually build a mapping of distinct AREAID/WORKGROUPID combinations.

Pros: This is probably the standard EntityFramework way of doing things, and doesn't require me to change any of the database structure or mappings.

Cons: Could potentially be slow, since the TPM_WORKGROUPS table is rather large and the TPM_AREAWORKGROUPS table only has 13 rows. Plus, there's no TPM_AREAWORKGROUPS class, so I'd have to return a collection of Tuples or make a new class for this.

2) Change my model

Ideally, I'd like a TPM_AREAWORKGROUP class, and a context.TPM_AREAWORKGROUP property. I used the designer to create this model directly from the database, so I'm not quite sure how to force this association to be an actual model. Is there an easy way to do this?

Pros: It would allow me to select directly against this table, done in one line of code. Yay!

Cons: Forces me to change my model, but is this a bad thing?

3) Screw it, use raw SQL to get what I want.

I can get the StoreConnection property of the context, and call CreateCommand() directly. I can then just do:

using (DbCommand cmd = conn.CreateCommand())
{
   cmd.CommandText = "SELECT AreaId, WorkgroupId FROM TPM_AREAWORKGROUPS";
   var reader = cmd.ExecuteReader();
   // Loop through and get each mapping
}

Pros: Fast, easy, doesn't require me to change my model.

Cons: Seems kind of hacky. Everywhere else in the project, we're just using standard Entity Framework code so this deviates from the norm. Also, it has the same issues as the first option; there's still no TPM_AREAWORKGROUPS class.

Question: What's the best solution for this problem?

Ideally, I'd like to do #2 however I'm not quite sure how to adjust my model. Or, perhaps someone knows of a better way than my three options.

Answer 1

You could do:

var result = context
    .TPM_WORKGROUPS
    .SelectMany(z => z.TPM_AREAS.Select(z2 => new
                    {
                        z2.AREAID,
                        z.WORKGROUPID
                    }));

The translated SQL will be a simple SELECT AREAID, WORKGROUPID FROM TPM_AREAWORKGROUPS.

About other options:

• I wouldn't use option 3) because I personnally avoid raw SQL as much as possible when using Entity Framework (see https://stackoverflow.com/a/8880157/870604 for some reasons).

• I wouldn't use option 2) because you would have to change your model, and there is a simple and efficient way that allows to not change it.