Entity Framework - Inner Join on Foreign Key

Question

I am using Entity Framework 4.0 (so I can use it with .NET 3.5) and I generated a DAL from an existing database.

In the database I have two tables with the following columns (Nothing is allowed to be NULL):

1. tblWeapon (WeaponId PK, WeaponLabel)
2. tblShot (ShotId PK, WeaponId)

And there's a foreign key on tblShot's WeaponId to tblWeapon.

Then the generated entities look something like this:

public class Weapon {
    public int WeaponId { ... }
    public string WeaponLabel { ...}
    public EntityCollection Shots { ... }
}

public class Shot {
    public int ShotId { ... }
    public EntityReference WeaponReference { ... }
    public Weapon Weapon { ... }
}

In my code I have a ShotFilter and a WeaponFilter classes that contain criteria to filter the individual tables by. Since the filter for the entities is dynamically generated, I would like to spread generation of the queries to the respective filter classes. Each filter would return an IQueryable<T> and they would be joined as needed to achieve the desired results.

What I want to do is get all the Shot objects that reference a weapon where the label contains the text 0.5.

The problem comes when trying to do an inner join on the IQueryable<Shot> to the IQueryable<Weapon> since Shot doesn't contain a WeaponId field (just a WeaponReference). After scouring the web and not finding much of anything, I found a forum post where the answer was to just join on the objects themselves. So I tried this and got the results I was expecting:

var oWeaponQuery = from w in oDc.Weapons select w;
oWeaponQuery = oWeaponQuery.Where(w => w.Label.Contains("0.5"));

var oShotQuery = from s in oDc.Shots select s;
oShotQuery = oShotQuery.Join(oWeaponQuery, s => s.Weapon, w => w, (s, w) => s);

But when I inspected the actual SQL queried using SQL Server Profiler, I saw this awful statement (and vomited a little):

SELECT 
1 AS [C1], 
[Extent1].[ShotId] AS [ShotId], 
[Extent1].[WeaponId] AS [WeaponId]
FROM  [dbo].[tblShot] AS [Extent1]
INNER JOIN [dbo].[tblWeapon] AS [Extent2] ON  EXISTS (SELECT 
    cast(1 as bit) AS [C1]
        FROM    ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]
        LEFT OUTER JOIN  (SELECT 
            [Extent3].[WeaponId] AS [WeaponId]
            FROM [dbo].[tblWeapon] AS [Extent3]
            WHERE [Extent1].[WeaponId] = [Extent3].[WeaponId] ) AS [Project1] ON 1 = 1
        LEFT OUTER JOIN  (SELECT 
            [Extent4].[WeaponId] AS [WeaponId]
            FROM [dbo].[tblWeapon] AS [Extent4]
            WHERE [Extent1].[WeaponId] = [Extent4].[WeaponId] ) AS [Project2] ON 1 = 1
        WHERE ([Project1].[WeaponId] = [Extent2].[WeaponId]) OR (([Project2].[WeaponId] IS NULL) AND ([Extent2].[WeaponId] IS NULL))
    )
WHERE (CAST(CHARINDEX(N'0.5', [Extent2].[Label]) AS int)) > 0

So, how would I do this in the correct or at least efficient manner? Or any other suggestions on how to organize my query generation in a dynamic and distributed manner?

Thanks!

---

Update With More Details

Part of my issue in doing the join is with EF, in the generated entity for Shot there is no WeaponId property. There's just the WeaponReference property that manages it. So in my join, I would expect to be able to use:

oShotQuery = oShotQuery.Join(oWeaponQuery, s => s.WeaponId, w => w.WeaponId, (s, w) => s);

But that doesn't work due to WeaponId not being a property of Shot.

Then I tried this (which again just seems wonky):

oShotQuery = oShotQuery.Join(oWeaponQuery, s => s.Weapon.WeaponId, w => w.WeaponId, (s, w) => s);

And that does work, and produces fairly concise SQL (with an exception):

SELECT 
1 AS [C1], 
[Extent1].[ShotId] AS [ShotId], 
[Extent1].[WeaponId] AS [WeaponId]
FROM  [dbo].[tblShot] AS [Extent1]
INNER JOIN [dbo].[tblWeapon] AS [Extent2] ON ([Extent1].[WeaponId] = [Extent2].[WeaponId]) OR 
(([Extent1].[WeaponId] IS NULL) AND ([Extent2].[WeaponId] IS NULL))
WHERE (CAST(CHARINDEX(N'0.5', [Extent2].[Label]) AS int)) > 0

And that exception is this: OR (([Extent1].[WeaponId] IS NULL) AND ([Extent2].[WeaponId] IS NULL)). I don't want where they're both NULL, I only want where they're equal.

Answer 1

What I want to do is get all the Shot objects that reference a weapon where the label contains the text 0.5.

This is the query I'd use:

var oShotQuery = oDc.Shots.Where(s => s.Weapon.Label.Contains("0.5"))

As far as the generated SQL is concerned, don't be put off if it throws in a bunch of stuff you wouldn't have thought to use. These queries tend to perform at least as well as any that you could generate manually. The key is to keep the original query simple.

Update

I want to be able to generate the different queries dynamically and then join them together later.

How about this?

var oShotQuery = oShotQuery.Where(
    o => oWeaponQuery.Any(w => w.WeaponId == o.Weapon.WeaponId))