LINQ Join not using datepart

Hi,

I'm having a problem with a LINQ join. The join compares both the month and year of a DateTime with the month and year of another DateTime. The problem I'm having is that the generated SQL compares the two DateTimes directly instead of using DATEPART to compare the month and year of the DateTimes.

Here's the C# LINQ: (note the join)

Db.Ctx.Logger = new Mindscape.LightSpeed.Logging.TraceLogger();
var targetsVsActualsByOrg = (from t in uow.Data_Desktop_Targets
                                join r in uow.Data_Desktop_Replacements on new { y = t.Month.Year, m = t.Month.Month, o = t.Config_OrganizationId } equals new { y = r.DateReplaced.Year, m = r.DateReplaced.Month, o = r.Config_OrganizationId ?? -1 }
                                where t.Month >= start && t.Month <= finish
                                group new { t.Month, t.Config_OrganizationId, t.Target, r.OldComputerName, r.NewComputerName } by new { ReplacementTarget = t.Target, OrgId = t.Config_OrganizationId, OrgName = t.Config_Organization.Abbreviation } into g
                                select new
                                {
                                    OrgId = g.Key.OrgId,
                                    OrgName = g.Key.OrgName,
                                    ReplacementTarget = g.Key.ReplacementTarget,
                                    ReplacementActual = g.Count()
                                }).ToArray().AsEnumerable();
Db.Ctx.Logger = null;

Here's the resulting SQL executed against our MS SQL db: (note the first inner join)

SELECT
  g1.Config_OrganizationId,
  g1.Target,
  g1.Abbreviation
FROM
  (
    SELECT
      t0.Config_OrganizationId AS [Config_OrganizationId],
      t0.Target AS [Target],
      Config_Organization.Abbreviation AS [Abbreviation]
    FROM
      Data_Desktop_Target t0
    INNER JOIN
      Data_Desktop_Replacement t1
    ON
      t0.Month = t1.DateReplaced AND t0.Month = t1.DateReplaced AND t0.Config_OrganizationId = t1.Config_OrganizationId
    INNER JOIN
      Config_Organization
    ON
      t0.Config_OrganizationId = Config_Organization.Id
    WHERE
      (t0.Month >= '11/1/2014 12:00:00 AM' AND t0.Month <= '11/30/2014 11:59:59 PM')
    GROUP BY
      t0.Target,
      t0.Config_OrganizationId,
      Config_Organization.Abbreviation
  )
  g1

If I convert the LINQ into a C# expression for LINQPad it generates the following SQL, which uses DATEPART for the join comparison:

SELECT COUNT(*) AS [ReplacementActual], [t0].[Target] AS [ReplacementTarget], [t0].[Config_OrganizationId] AS [OrgId], [t2].[Abbreviation] AS [OrgName]
FROM [Data_Desktop_Target] AS [t0]
INNER JOIN [Data_Desktop_Replacement] AS [t1] ON (DATEPART(Year, [t0].[Month]) = DATEPART(Year, [t1].[DateReplaced])) AND (DATEPART(Month, [t0].[Month]) = DATEPART(Month, [t1].[DateReplaced])) AND ([t0].[Config_OrganizationId] = (COALESCE([t1].[Config_OrganizationId],@p0)))
INNER JOIN [Config_Organization] AS [t2] ON [t2].[Id] = [t0].[Config_OrganizationId]
WHERE ([t0].[Month] >= @p1) AND ([t0].[Month] <= @p2)
GROUP BY [t0].[Target], [t0].[Config_OrganizationId], [t2].[Abbreviation]

Could you let me know if this is by-design / a bug / or user error?

Thanks for your help, Alex

(edited to fix typo)

This looks like a bug - I will have a look into why this isn't translating out to use that function.

Jeremy

Just as a follow up to this, we dont currently support functions on join criteria which is why this isnt being translated so for now Im afraid its not possible to express this and Ive added an item to our backlog to look at adding this in.

Jeremy

Hi Jeremy, thanks for investigating this further and keeping us in the loop with the follow-up.

Cheers, Alex