Geek asks and answers

Is there a quick way to get every connection between two objects?

There are two tables in my database: TPM_AREASand TPM_WORKGROUPS. There is a many-to-many relationship between the two tables, and these relationships are stored in a table named TPM_AREAWORKGROUPS. This table looks like this:

enter image description here

What I need to do is load all of these mappings into memory at once, in the fastest way. Since it TPM_AREAWORKGROUPSis an association, I cannot just say:

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

I can come up with three ways to do this, but I'm not quite sure how to accomplish each of them, and the other is the best.

1) Download each workgroup, including related areas:

Sort of:

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.

: , , EntityFramework - .

: , TPM_WORKGROUPS , TPM_AREAWORKGROUPS 13 . , TPM_AREAWORKGROUPS, Tuples .

2)

TPM_AREAWORKGROUP context.TPM_AREAWORKGROUP. , , . ?

: , . !

: , ?

3) , raw SQL, , .

StoreConnection CreateCommand(). :

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

: , , , .

: , . Entity Framework, . , , ; TPM_AREAWORKGROUPS .

: ?

, # 2, , . , , - , .

+5
2

:

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

SQL SELECT AREAID, WORKGROUPID FROM TPM_AREAWORKGROUPS.

:

+3

?

, , , .

0

More articles:


All Articles