Here is the LINQ query, as copied from LINQPad 4 (language = "C# Program"):

public class ForAdPlacementResultset
{
    public int AdKeywordID { get; set; }
    public int AdID { get; set; }
    public string AdKeywordText { get; set; }
    public string MediaURI { get; set; }
    public int AdSpecialtyID {get;set;}
    public int AdKeywordSpecialtyID {get;set;}
}
    
void Main()
{           
    int timeZoneID = 5; 
    int stateID = 0; 
    int? specialtyID = null; 
    string zipCode = null; 
    DateTime? documentApprovalDate = new DateTime(2010, 8, 10);
    
    using (DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData meta = new LinqMetaData(adapter);
    
        var adTables = from a in meta.Ad
                    join ak in meta.AdKeyword on a.AdID equals ak.AdID
                    join asp in meta.AdSpecialty on ak.AdID equals asp.AdID into asp_oj
                    from x in asp_oj.DefaultIfEmpty()
                    where (a.StartDate == null || a.StartDate < documentApprovalDate)
                            && (a.EndDate == null || a.EndDate > documentApprovalDate)
                            && (a.TimeZoneID == null || a.TimeZoneID == timeZoneID) 
                            && (a.StateID == null || a.StateID == stateID)
                    select new { a, ak, x, x.SpecialtyID };
    
        if (documentApprovalDate.HasValue)
            adTables = adTables.Where(at => (at.a.StartDate == null || at.a.StartDate < documentApprovalDate)
                                            && (at.a.EndDate == null || at.a.EndDate > documentApprovalDate));
    
        if (!string.IsNullOrEmpty(zipCode))
            adTables = adTables.Where(at => (at.a.ZipCode == null || at.a.ZipCode.Equals(zipCode)));
    
        if (specialtyID.HasValue)
            adTables = adTables.Where(at => at.x.SpecialtyID == specialtyID);
        
        var forAdPlacementResultset =   from at in adTables
                                        select new
                                        {
                                            AdKeywordID = at.ak.AdKeywordID,
                                            AdID = at.a.AdID,
                                            AdKeywordText = at.ak.AdKeywordText,
                                            MediaURI = at.a.MediaURI,
                                            AdSpecialtyID = at.SpecialtyID,
                                            AdKeywordSpecialtyID = at.x.SpecialtyID //this is the wrong SpecialtyID, is this a bug?
                                        };
        
        forAdPlacementResultset.Dump();
    }
}

and here is the SQL Server 2008 script to create the tables along with a few lines of data:

CREATE TABLE [dbo].[Ads](
    [AdID] [int] IDENTITY(1,1) NOT NULL,
    [AdvertiserInvolvedPartyID] [int] NOT NULL,
    [MediaURI] [varchar](50) NOT NULL,
    [UpdatedDate] [datetime] NOT NULL,
    [AdStatusTypeID] [int] NOT NULL,
    [AdName] [varchar](50) NULL,
    [SpecialtyID] [int] NULL,
    [StateID] [int] NULL,
    [TimeZoneID] [int] NULL,
    [ZipCode] [nchar](10) NULL,
    [StartDate] [datetime] NULL,
    [EndDate] [datetime] NULL,
    [DisclaimerURI] [varchar](50) NULL,
 CONSTRAINT [PK_Ads] PRIMARY KEY CLUSTERED 
(
    [AdID] ASC
))
GO

SET IDENTITY_INSERT [dbo].[Ads] ON
INSERT [dbo].[Ads] ([AdID], [AdvertiserInvolvedPartyID], [MediaURI], [UpdatedDate], [AdStatusTypeID], [AdName], [SpecialtyID], [StateID], [TimeZoneID], [ZipCode], [StartDate], [EndDate], [DisclaimerURI]) VALUES (59, 1489, N'Downloads/becomenanex5.jpg', CAST(0x00009C31007DE0B2 AS DateTime), 3, N'Become an Ex Campaign', NULL, NULL, NULL, NULL, CAST(0x00009C3000000000 AS DateTime), NULL, N'/LEGFOU9134_7x10_5A_DownloadPDF_HR.pdf')
INSERT [dbo].[Ads] ([AdID], [AdvertiserInvolvedPartyID], [MediaURI], [UpdatedDate], [AdStatusTypeID], [AdName], [SpecialtyID], [StateID], [TimeZoneID], [ZipCode], [StartDate], [EndDate], [DisclaimerURI]) VALUES (62, 1190, N'Downloads/boquita1.jpg', CAST(0x00009C8500D3F6F7 AS DateTime), 4, N'LETS SEE NOW!', NULL, NULL, NULL, NULL, NULL, NULL, N'')
INSERT [dbo].[Ads] ([AdID], [AdvertiserInvolvedPartyID], [MediaURI], [UpdatedDate], [AdStatusTypeID], [AdName], [SpecialtyID], [StateID], [TimeZoneID], [ZipCode], [StartDate], [EndDate], [DisclaimerURI]) VALUES (63, 1190, N'//Adverts/boca.jpg', CAST(0x00009CA800A7B6E7 AS DateTime), 3, N'LIVE AD TESTER', NULL, NULL, NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[Ads] ([AdID], [AdvertiserInvolvedPartyID], [MediaURI], [UpdatedDate], [AdStatusTypeID], [AdName], [SpecialtyID], [StateID], [TimeZoneID], [ZipCode], [StartDate], [EndDate], [DisclaimerURI]) VALUES (65, 1190, N'///Adverts/Boca.jpg', CAST(0x00009DAE00CB60B2 AS DateTime), 4, N'2010 TEST', NULL, NULL, 18, NULL, CAST(0x00009DA300000000 AS DateTime), CAST(0x00009DA300000000 AS DateTime), NULL)
INSERT [dbo].[Ads] ([AdID], [AdvertiserInvolvedPartyID], [MediaURI], [UpdatedDate], [AdStatusTypeID], [AdName], [SpecialtyID], [StateID], [TimeZoneID], [ZipCode], [StartDate], [EndDate], [DisclaimerURI]) VALUES (72, 1190, N'Adverts/VivaLogo_rev3_small.jpg', CAST(0x00009DE40124F3DA AS DateTime), 3, N'my ampersand ad', NULL, NULL, NULL, NULL, CAST(0x00009DCD00000000 AS DateTime), CAST(0x00009E1700000000 AS DateTime), NULL)
SET IDENTITY_INSERT [dbo].[Ads] OFF

CREATE TABLE [dbo].[AdSpecialty](
    [AdSpecialtyID] [int] IDENTITY(1,1) NOT NULL,
    [SpecialtyID] [int] NOT NULL,
    [AdID] [int] NOT NULL,
 CONSTRAINT [PK_AdSpecialty] PRIMARY KEY CLUSTERED 
(
    [AdSpecialtyID] ASC
)
) 
GO

SET IDENTITY_INSERT [dbo].[AdSpecialty] ON
INSERT [dbo].[AdSpecialty] ([AdSpecialtyID], [SpecialtyID], [AdID]) VALUES (1, 7, 59)
INSERT [dbo].[AdSpecialty] ([AdSpecialtyID], [SpecialtyID], [AdID]) VALUES (2, 9, 59)
INSERT [dbo].[AdSpecialty] ([AdSpecialtyID], [SpecialtyID], [AdID]) VALUES (3, 10, 59)
INSERT [dbo].[AdSpecialty] ([AdSpecialtyID], [SpecialtyID], [AdID]) VALUES (4, 17, 59)
INSERT [dbo].[AdSpecialty] ([AdSpecialtyID], [SpecialtyID], [AdID]) VALUES (5, 20, 59)
INSERT [dbo].[AdSpecialty] ([AdSpecialtyID], [SpecialtyID], [AdID]) VALUES (6, 22, 59)
INSERT [dbo].[AdSpecialty] ([AdSpecialtyID], [SpecialtyID], [AdID]) VALUES (7, 23, 59)
INSERT [dbo].[AdSpecialty] ([AdSpecialtyID], [SpecialtyID], [AdID]) VALUES (8, 44, 59)
INSERT [dbo].[AdSpecialty] ([AdSpecialtyID], [SpecialtyID], [AdID]) VALUES (9, 61, 59)
INSERT [dbo].[AdSpecialty] ([AdSpecialtyID], [SpecialtyID], [AdID]) VALUES (14, 4, 63)
INSERT [dbo].[AdSpecialty] ([AdSpecialtyID], [SpecialtyID], [AdID]) VALUES (20, 2, 72)
INSERT [dbo].[AdSpecialty] ([AdSpecialtyID], [SpecialtyID], [AdID]) VALUES (21, 4, 72)
INSERT [dbo].[AdSpecialty] ([AdSpecialtyID], [SpecialtyID], [AdID]) VALUES (22, 6, 72)
INSERT [dbo].[AdSpecialty] ([AdSpecialtyID], [SpecialtyID], [AdID]) VALUES (23, 7, 72)
INSERT [dbo].[AdSpecialty] ([AdSpecialtyID], [SpecialtyID], [AdID]) VALUES (24, 10, 72)
SET IDENTITY_INSERT [dbo].[AdSpecialty] OFF

CREATE TABLE [dbo].[AdKeywords](
    [AdKeywordID] [int] IDENTITY(1,1) NOT NULL,
    [AdID] [int] NOT NULL,
    [AdKeywordText] [varchar](50) NOT NULL,
    [AdKeywordIsNot] [bit] NOT NULL,
 CONSTRAINT [PK_AdKeywords] PRIMARY KEY CLUSTERED 
(
    [AdKeywordID] ASC
)
)
GO

SET IDENTITY_INSERT [dbo].[AdKeywords] ON
INSERT [dbo].[AdKeywords] ([AdKeywordID], [AdID], [AdKeywordText], [AdKeywordIsNot]) VALUES (301, 63, N'superfantastique', 0)
INSERT [dbo].[AdKeywords] ([AdKeywordID], [AdID], [AdKeywordText], [AdKeywordIsNot]) VALUES (309, 59, N'asdfsdaf sdf', 0)
INSERT [dbo].[AdKeywords] ([AdKeywordID], [AdID], [AdKeywordText], [AdKeywordIsNot]) VALUES (314, 72, N'ampersandy', 0)
SET IDENTITY_INSERT [dbo].[AdKeywords] OFF

Here is the debug information, including the generated SQL:

Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
  Query: SELECT [LPA_L3].[AdID], [LPA_L3].[AdName], [LPA_L3].[AdStatusTypeID], [LPA_L3].[AdvertiserInvolvedPartyID], [LPA_L3].[DisclaimerURI], [LPA_L3].[EndDate], [LPA_L3].[MediaURI], [LPA_L3].[SpecialtyID], [LPA_L3].[StartDate], [LPA_L3].[StateID], [LPA_L3].[TimeZoneID], [LPA_L3].[UpdatedDate], [LPA_L3].[ZipCode], [LPA_L4].[AdID] AS [AdID0], [LPA_L4].[AdKeywordID], [LPA_L4].[AdKeywordIsNot], [LPA_L4].[AdKeywordText], [LPA_L5].[AdID] AS [AdID1], [LPA_L5].[AdSpecialtyID], [LPA_L5].[SpecialtyID] AS [SpecialtyID2] FROM (( [dbo].[Ads] [LPA_L3]  INNER JOIN [dbo].[AdKeywords] [LPA_L4]  ON  [LPA_L3].[AdID] = [LPA_L4].[AdID]) LEFT JOIN [dbo].[AdSpecialty] [LPA_L5]  ON  [LPA_L4].[AdID] = [LPA_L5].[AdID]) WHERE ( ( ( ( ( ( ( [LPA_L3].[StartDate] IS NULL) OR ( [LPA_L3].[StartDate] < @p1)) AND ( ( [LPA_L3].[EndDate] IS NULL) OR ( [LPA_L3].[EndDate] > @p2))) AND ( ( [LPA_L3].[TimeZoneID] IS NULL) OR ( [LPA_L3].[TimeZoneID] = @p3))) AND ( ( [LPA_L3].[StateID] IS NULL) OR ( [LPA_L3].[StateID] = @p4)))))
  Parameter: @p1 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 8/10/2010 12:00:00 AM.
  Parameter: @p2 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 8/10/2010 12:00:00 AM.
  Parameter: @p3 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 5.
  Parameter: @p4 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 0.
Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Generated Sql query: 
  Query: SELECT [LPA_L1].[AdKeywordID], [LPA_L1].[AdID], [LPA_L1].[AdKeywordText], [LPA_L1].[MediaURI], [LPA_L1].[SpecialtyID] AS [AdSpecialtyID], [LPA_L1].[SpecialtyID] AS [AdKeywordSpecialtyID] FROM (SELECT [LPA_L3].[AdID], [LPA_L3].[AdName], [LPA_L3].[AdStatusTypeID], [LPA_L3].[AdvertiserInvolvedPartyID], [LPA_L3].[DisclaimerURI], [LPA_L3].[EndDate], [LPA_L3].[MediaURI], [LPA_L3].[SpecialtyID], [LPA_L3].[StartDate], [LPA_L3].[StateID], [LPA_L3].[TimeZoneID], [LPA_L3].[UpdatedDate], [LPA_L3].[ZipCode], [LPA_L4].[AdID] AS [AdID0], [LPA_L4].[AdKeywordID], [LPA_L4].[AdKeywordIsNot], [LPA_L4].[AdKeywordText], [LPA_L5].[AdID] AS [AdID1], [LPA_L5].[AdSpecialtyID], [LPA_L5].[SpecialtyID] AS [SpecialtyID2] FROM (( [dbo].[Ads] [LPA_L3]  INNER JOIN [dbo].[AdKeywords] [LPA_L4]  ON  [LPA_L3].[AdID] = [LPA_L4].[AdID]) LEFT JOIN [dbo].[AdSpecialty] [LPA_L5]  ON  [LPA_L4].[AdID] = [LPA_L5].[AdID]) WHERE ( ( ( ( ( ( ( [LPA_L3].[StartDate] IS NULL) OR ( [LPA_L3].[StartDate] < @p1)) AND ( ( [LPA_L3].[EndDate] IS NULL) OR ( [LPA_L3].[EndDate] > @p2))) AND ( ( [LPA_L3].[TimeZoneID] IS NULL) OR ( [LPA_L3].[TimeZoneID] = @p3))) AND ( ( [LPA_L3].[StateID] IS NULL) OR ( [LPA_L3].[StateID] = @p4)))))) [LPA_L1] WHERE ( ( ( ( ( ( ( [LPA_L1].[StartDate] IS NULL) OR ( [LPA_L1].[StartDate] < @p5)) AND ( ( [LPA_L1].[EndDate] IS NULL) OR ( [LPA_L1].[EndDate] > @p6)))))))
  Parameter: @p1 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 8/10/2010 12:00:00 AM.
  Parameter: @p2 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 8/10/2010 12:00:00 AM.
  Parameter: @p3 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 5.
  Parameter: @p4 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 0.
  Parameter: @p5 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 8/10/2010 12:00:00 AM.
  Parameter: @p6 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 8/10/2010 12:00:00 AM.
Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.

There is no inheritance or anything fancy with the Entitites.

Ok, basically my problem is that AdSpecialtyID and AdKeywordSpecialtyID are both using the SpecialtyID column of the Ads table. In the data provided, both columns are null, where in reality only AdSpecialtyID should be null.

at.x.SpecialtyID

should access the AdSpecialty table. Am I doing something wrong, or is this possibly a bug?

Thank you for your attention.

Buildnr/runtime lib version you're using would be great

Otis wrote:

Buildnr/runtime lib version you're using would be great

Oops! I'm surprised that's not included in the debug info.

DQE.SqlServer.NET20.dll : 3.0.10.0729 LinqSupportClasses.NET35.dll: 3.0.10.0809 ORMSupportClasses.NET20.dll: 3.0.10.0817

all of which are from the August 18th, 2010 build.

Dont at.SpecialityId and at.x.SpecialityId both point to the same field...?

You're right. I apologize for that. I screwed up when I was preparing the query for the forum. (Sorry about that! ) Here is the query which demonstrates the problem.

public class ForAdPlacementResultset
{
    public int AdKeywordID { get; set; }
    public int AdID { get; set; }
    public string AdKeywordText { get; set; }
    public string MediaURI { get; set; }
    public int AdSpecialtyID {get;set;}
    public int AdSpecialtySpecialtyID {get;set;}
}
    
void Main()
{           
    int timeZoneID = 5; 
    int stateID = 0; 
    int? specialtyID = null; 
    string zipCode = null; 
    DateTime? documentApprovalDate = new DateTime(2010, 8, 10);
    
    using (DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData meta = new LinqMetaData(adapter);
    
        var adTables = from a in meta.Ad
                    join ak in meta.AdKeyword on a.AdID equals ak.AdID
                    join asp in meta.AdSpecialty on ak.AdID equals asp.AdID into asp_oj
                    from x in asp_oj.DefaultIfEmpty()
                    where (a.StartDate == null || a.StartDate < documentApprovalDate)
                            && (a.EndDate == null || a.EndDate > documentApprovalDate)
                            && (a.TimeZoneID == null || a.TimeZoneID == timeZoneID) 
                            && (a.StateID == null || a.StateID == stateID)
                    select new { a, ak, x };
    
        if (documentApprovalDate.HasValue)
            adTables = adTables.Where(at => (at.a.StartDate == null || at.a.StartDate < documentApprovalDate)
                                            && (at.a.EndDate == null || at.a.EndDate > documentApprovalDate));
    
        if (!string.IsNullOrEmpty(zipCode))
            adTables = adTables.Where(at => (at.a.ZipCode == null || at.a.ZipCode.Equals(zipCode)));
    
        if (specialtyID.HasValue)
            adTables = adTables.Where(at => at.x.SpecialtyID == specialtyID);
        
        var forAdPlacementResultset =   from at in adTables
                                        select new ForAdPlacementResultset
                                        {
                                            AdKeywordID = at.ak.AdKeywordID,
                                            AdID = at.a.AdID,
                                            AdKeywordText = at.ak.AdKeywordText,
                                            MediaURI = at.a.MediaURI,
                                            AdSpecialtyID = at.a.SpecialtyID,
                                            AdSpecialtySpecialtyID = at.x.SpecialtyID //this is the wrong SpecialtyID, is this a bug?
                                        };
        
        forAdPlacementResultset.Dump();
    }
}

and the debug info

Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
  Query: SELECT [LPA_L3].[AdID], [LPA_L3].[AdName], [LPA_L3].[AdStatusTypeID], [LPA_L3].[AdvertiserInvolvedPartyID], [LPA_L3].[DisclaimerURI], [LPA_L3].[EndDate], [LPA_L3].[MediaURI], [LPA_L3].[SpecialtyID], [LPA_L3].[StartDate], [LPA_L3].[StateID], [LPA_L3].[TimeZoneID], [LPA_L3].[UpdatedDate], [LPA_L3].[ZipCode], [LPA_L4].[AdID] AS [AdID0], [LPA_L4].[AdKeywordID], [LPA_L4].[AdKeywordIsNot], [LPA_L4].[AdKeywordText], [LPA_L5].[AdID] AS [AdID1], [LPA_L5].[AdSpecialtyID], [LPA_L5].[SpecialtyID] AS [SpecialtyID2] FROM (( [dbo].[Ads] [LPA_L3]  INNER JOIN [dbo].[AdKeywords] [LPA_L4]  ON  [LPA_L3].[AdID] = [LPA_L4].[AdID]) LEFT JOIN [dbo].[AdSpecialty] [LPA_L5]  ON  [LPA_L4].[AdID] = [LPA_L5].[AdID]) WHERE ( ( ( ( ( ( ( [LPA_L3].[StartDate] IS NULL) OR ( [LPA_L3].[StartDate] < @p1)) AND ( ( [LPA_L3].[EndDate] IS NULL) OR ( [LPA_L3].[EndDate] > @p2))) AND ( ( [LPA_L3].[TimeZoneID] IS NULL) OR ( [LPA_L3].[TimeZoneID] = @p3))) AND ( ( [LPA_L3].[StateID] IS NULL) OR ( [LPA_L3].[StateID] = @p4)))))
  Parameter: @p1 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 8/10/2010 12:00:00 AM.
  Parameter: @p2 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 8/10/2010 12:00:00 AM.
  Parameter: @p3 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 5.
  Parameter: @p4 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 0.
Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Generated Sql query: 
  Query: SELECT [LPA_L1].[AdKeywordID], [LPA_L1].[AdID], [LPA_L1].[AdKeywordText], [LPA_L1].[MediaURI], [LPA_L1].[SpecialtyID] AS [AdSpecialtyID], [LPA_L1].[SpecialtyID] AS [AdSpecialtySpecialtyID] FROM (SELECT [LPA_L3].[AdID], [LPA_L3].[AdName], [LPA_L3].[AdStatusTypeID], [LPA_L3].[AdvertiserInvolvedPartyID], [LPA_L3].[DisclaimerURI], [LPA_L3].[EndDate], [LPA_L3].[MediaURI], [LPA_L3].[SpecialtyID], [LPA_L3].[StartDate], [LPA_L3].[StateID], [LPA_L3].[TimeZoneID], [LPA_L3].[UpdatedDate], [LPA_L3].[ZipCode], [LPA_L4].[AdID] AS [AdID0], [LPA_L4].[AdKeywordID], [LPA_L4].[AdKeywordIsNot], [LPA_L4].[AdKeywordText], [LPA_L5].[AdID] AS [AdID1], [LPA_L5].[AdSpecialtyID], [LPA_L5].[SpecialtyID] AS [SpecialtyID2] FROM (( [dbo].[Ads] [LPA_L3]  INNER JOIN [dbo].[AdKeywords] [LPA_L4]  ON  [LPA_L3].[AdID] = [LPA_L4].[AdID]) LEFT JOIN [dbo].[AdSpecialty] [LPA_L5]  ON  [LPA_L4].[AdID] = [LPA_L5].[AdID]) WHERE ( ( ( ( ( ( ( [LPA_L3].[StartDate] IS NULL) OR ( [LPA_L3].[StartDate] < @p1)) AND ( ( [LPA_L3].[EndDate] IS NULL) OR ( [LPA_L3].[EndDate] > @p2))) AND ( ( [LPA_L3].[TimeZoneID] IS NULL) OR ( [LPA_L3].[TimeZoneID] = @p3))) AND ( ( [LPA_L3].[StateID] IS NULL) OR ( [LPA_L3].[StateID] = @p4)))))) [LPA_L1] WHERE ( ( ( ( ( ( ( [LPA_L1].[StartDate] IS NULL) OR ( [LPA_L1].[StartDate] < @p5)) AND ( ( [LPA_L1].[EndDate] IS NULL) OR ( [LPA_L1].[EndDate] > @p6)))))))
  Parameter: @p1 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 8/10/2010 12:00:00 AM.
  Parameter: @p2 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 8/10/2010 12:00:00 AM.
  Parameter: @p3 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 5.
  Parameter: @p4 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 0.
  Parameter: @p5 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 8/10/2010 12:00:00 AM.
  Parameter: @p6 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 8/10/2010 12:00:00 AM.
Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.

I think I reproduced with the latest RTL (3.0.10.0831). I removed some irrelevant lines. So here it goes:

This works:

[TestMethod]
public void JustOneQuery()
{
    using (DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData meta = new LinqMetaData(adapter);

        var adPlacement1 = (from a in meta.Ad
                       join ak in meta.AdKeyword on a.AdId equals ak.AdId
                       join asp in meta.AdSpecialty on ak.AdId equals asp.AdId into asp_oj
                       from x in asp_oj.DefaultIfEmpty()
                       select new ForAdPlacementResultset
                                       {
                                           AdKeywordID = ak.AdKeywordId,
                                           AdID = a.AdId,
                                           AdKeywordText = ak.AdKeywordText,
                                           MediaURI = a.MediaUri,
                                           AdSpecialtyID = a.SpecialtyId,
                                           AdSpecialtySpecialtyID = x.SpecialtyId
                                       }).First();

        Assert.AreNotEqual(adPlacement1.AdSpecialtyID, adPlacement1.AdSpecialtySpecialtyID);                            
    }
}

This fails the test:

[TestMethod]
public void TwoQueries()
{
    using (DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData meta = new LinqMetaData(adapter);

        var adTables = from a in meta.Ad
                        join ak in meta.AdKeyword on a.AdId equals ak.AdId
                        join asp in meta.AdSpecialty on ak.AdId equals asp.AdId into asp_oj
                        from x in asp_oj.DefaultIfEmpty()
                       select new { a, ak, x };
        
        var adPlacement1 = (from at in adTables
                           select new ForAdPlacementResultset
                           {
                               AdKeywordID = at.ak.AdKeywordId,
                               AdID = at.a.AdId,
                               AdKeywordText = at.ak.AdKeywordText,
                               MediaURI = at.a.MediaUri,
                               AdSpecialtyID = at.a.SpecialtyId,
                               AdSpecialtySpecialtyID = at.x.SpecialtyId
                           }).First();

        Assert.AreNotEqual(adPlacement1.AdSpecialtyID, adPlacement1.AdSpecialtySpecialtyID);
    }
}

And in the second case, the relevant part of the Generated SQL is the main select:

SELECT 
    [LPA_L1].[AdKeywordId] AS [AdKeywordID], 
    [LPA_L1].[AdId] AS [AdID], 
    [LPA_L1].[AdKeywordText], 
    [LPA_L1].[MediaUri] AS [MediaURI], 
    [LPA_L1].[SpecialtyId] AS [AdSpecialtyID], 
    [LPA_L1].[SpecialtyId] AS [AdSpecialtySpecialtyID] 
...
FROM 
    (SELECT 
                ...
        [LPA_L5].[AdSpecialtyID] AS [AdSpecialtyId], 
        [LPA_L5].[SpecialtyID] AS [SpecialtyId2] 
    FROM 
         ([AdsTest].[dbo].[Ads] [LPA_L3]  
            INNER JOIN [AdsTest].[dbo].[AdKeywords] [LPA_L4]  ON  [LPA_L3].[AdID] = [LPA_L4].[AdID])
            LEFT JOIN [AdsTest].[dbo].[AdSpecialty] [LPA_L5]  ON  [LPA_L4].[AdID] = [LPA_L5].[AdID]
    ) [LPA_L1]

As you see, the same field is selected in the main SELECT when it should be (I think):

SELECT 
    ...
    [LPA_L1].[SpecialtyId] AS [AdSpecialtyID], 
    [LPA_L1].[SpecialtyId2] AS [AdSpecialtySpecialtyID]

We are looking into this. In the meantime please use the first approach (one query).

This is the same problem as: from o in metaData.Order select new { o.Employee.Employee.Employee.EmployeeId, o.Employee.EmployeeId}

the thing is that we assign aliases to members but not according to where they're located. This means that the member might look like X and thus gets X's alias, but it's really the same member on another instance over another path.

We can't fix this at this point as it requires a deep change in the linq provider. We are aware of this problem but fixing it will require we have to rewrite a larger part of the linq provider and therefore have postponed it to a later date where we will revisit the linq provider with a new preprocessor pipeline (so alias issues and other crap won't take place anymore. )

Please use the workaround.

Thank you for your explanation. However, the suggested workaround wouldn't work for me because of the conditional where clauses. This relates back to the other thread about the PredicateBuilder and what benefit it really brings. You know what I mean? I can't use one query because of this issue:

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=16399&HighLight=1

and--unless I am missing something, which is always a very real possibility --neither the PredicateBuilder nor the method I was using to accomplish conditional 'filters' can be done using a single LINQ query. Am I wrong? I mean, essentially what I need to do, as posted in my original query including the conditional where clauses, is impossible in LINQ-to-LLBLGen (_EDIT: without resorting to a big if statement which repeats the entire query again, as suggested in the thread I linked to above_), and therefore this query will remain a stored procedure for the time being. That's not the end of the world, of course, but it's a shame considering the query is not really all that complex, and I will be coming across a lot more queries of the same type in the conversion that I am doing.

I await your further thoughts. Thanks.

In that case, PredicateBuilder could help you. You first build your predicate. Use the 1-query workaround and then do a query.Where(thePredicate). Sorry for the inconvenient but that should work.

I don't think so--again, unless I am missing something. I need to filter on the SpecialtyID that is part of the outer join, named x in the query. The following code compiles...

var predicate = PredicateBuilder.Null<AdEntity>();

if (!string.IsNullOrEmpty(zipCode))
    predicate = predicate.And(a => (a.ZipCode == null || a.ZipCode.Equals(zipCode)));

if (specialtyID.HasValue)
    predicate = predicate.And(x => x.SpecialtyID == specialtyID);

...but is filtering the SpecialtyID in the Ads table, Ads, not the SpecialtyID in the AdSpecialty table. This makes sense, of course, because the generic PredicateBuilder was specified with <AdEntity>. So that doesn't work for me.

The following doesn't compile:

if (specialtyID.HasValue)
    predicate = predicate.And<AdSpecialtyEntity>(x => x.SpecialtyID == specialtyID);

Neither does the following compile:

var predicate1 = PredicateBuilder.Null<AdEntity>();
var predicate2 = PredicateBuilder.Null<AdSpecialtyEntity>();

if (documentApprovalDate.HasValue)
    predicate1 = predicate1.And(a => (a.StartDate == null || a.StartDate < documentApprovalDate)
                                    && (a.EndDate == null || a.EndDate > documentApprovalDate));

if (!string.IsNullOrEmpty(zipCode))
    predicate1 = predicate1.And(a => (a.ZipCode == null || a.ZipCode.Equals(zipCode)));

if (specialtyID.HasValue)
    predicate2 = predicate2.And(x => x.SpecialtyID == specialtyID);

var data = adTables.Where(predicate1).Where(predicate2).ToList();

As I recall now, the inability to filter on columns not included in the select new { a.Column1 } is why I was forced to use the 2 query approach in the first place. Now do you see my dilemma? Thank you for your continued attention; I'm interested in what further advice you can offer.

I understand you. After your explanation my best recommendation to you is to use LLBLGen Pro API classes.