I have a following classes
class User
{
[Column]public int Id;
[Column]public string Name;
[Column]public Company Company;
}
class Company
{
[Column]public int Id;
[Column]public string Name;
[Column]public Address Address;
}
class Address
{
[Column]public int Id;
[Column]public string Street;
[Column]public string City;
[Column]public string State;
}
I have ommitted several association properties for brevity. As you can see each user belongs to one company and a company has 1 address.
for the simple query:
var users = from c in db.Users select c;
When I use:
var dataLoadOptions = new DataLoadOptions();
dataLoadOptions.LoadWith<User>(x => x.Actor);
I get back a single LEFT OUTER JOIN . But when I use
var dataLoadOptions = new DataLoadOptions();
dataLoadOptions.LoadWith<User>(x => x.Actor);
dataLoadOptions.LoadWith<Actor>(x => x.Address);
I get back six LEFT OUTER JOIN instead of two with a lot of unnecessary columns all joining to the same property
AS [t3] ON [t3].[AddressID] = [t1].[AddressID]
AS [t5] ON [t5].[AddressID] = [t1].[AddressID]
AS [t7] ON [t7].[AddressID] = [t1].[AddressID]
AS [t9] ON [t9].[AddressID] = [t1].[AddressID]
Is this a bug in Linq2Sql or am I missing something?
UPDATE Below is the SQL output from the Linq Query
SELECT [t0].[Name] AS [Name], [t0].[UserName] AS [LoginId], [t0].[CompanyID] AS [CompanyId], [t18].[test], [t18].[CompanyTypeID] AS [CompanyType], [t18].[Name] AS [Name2], [t18].[CompanyGuid] AS [Guid], [t18].[CompanyID] AS [Id], [t18].[AddressID] AS [AddressId], [t18].[test2], [t18].[BusinessName], [t18].[Address1], [t18].[Address2], [t18].[City], [t18].[StateCode], [t18].[PostalCode], [t18].[AddressID2] AS [Id2], [t18].[CreatedByUserId] AS [CreatedByUserId], [t18].[LastModifiedByUserId] AS [LastModifiedByUserId], [t18].[IsActive], [t18].[IsLocked], [t18].[CreationDate], [t18].[LastModificationDate], [t18].[CreatedByUserId2] AS [CreatedByUserId2], [t18].[LastModifiedByUserId2] AS [LastModifiedByUserId2], [t18].[IsActive2], [t18].[IsLocked2], [t18].[CreationDate2], [t18].[LastModificationDate2], [t18].[test3], [t18].[BusinessName2], [t18].[Address12], [t18].[Address22], [t18].[City2], [t18].[StateCode2], [t18].[PostalCode2], [t18].[AddressID3] AS [Id3], [t18].[CreatedByUserId3] AS [CreatedByUserId3], [t18].[LastModifiedByUserId3] AS [LastModifiedByUserId3], [t18].[IsActive3], [t18].[IsLocked3], [t18].[CreationDate3], [t18].[LastModificationDate3], [t18].[test4], [t18].[BusinessName3], [t18].[Address13], [t18].[Address23], [t18].[City3], [t18].[StateCode3], [t18].[PostalCode3], [t18].[AddressID4] AS [Id4], [t18].[CreatedByUserId4] AS [CreatedByUserId4], [t18].[LastModifiedByUserId4] AS [LastModifiedByUserId4], [t18].[IsActive4], [t18].[IsLocked4], [t18].[CreationDate4], [t18].[LastModificationDate4], [t18].[test5], [t18].[BusinessName4], [t18].[Address14], [t18].[Address24], [t18].[City4], [t18].[StateCode4], [t18].[PostalCode4], [t18].[AddressID5] AS [Id5], [t18].[CreatedByUserId5] AS [CreatedByUserId5], [t18].[LastModifiedByUserId5] AS [LastModifiedByUserId5], [t18].[IsActive5], [t18].[IsLocked5], [t18].[CreationDate5], [t18].[LastModificationDate5], [t18].[test6], [t18].[BusinessName5], [t18].[Address15], [t18].[Address25], [t18].[City5], [t18].[StateCode5], [t18].[PostalCode5], [t18].[AddressID6] AS [Id6], [t18].[CreatedByUserId6] AS [CreatedByUserId6], [t18].[LastModifiedByUserId6] AS [LastModifiedByUserId6], [t18].[IsActive6], [t18].[IsLocked6], [t18].[CreationDate6], [t18].[LastModificationDate6], [t18].[test7], [t18].[BusinessName6], [t18].[Address16], [t18].[Address26], [t18].[City6], [t18].[StateCode6], [t18].[PostalCode6], [t18].[AddressID7] AS [Id7], [t18].[CreatedByUserId7] AS [CreatedByUserId7], [t18].[LastModifiedByUserId7] AS [LastModifiedByUserId7], [t18].[IsActive7], [t18].[IsLocked7], [t18].[CreationDate7], [t18].[LastModificationDate7], [t18].[test8], [t18].[BusinessName7], [t18].[Address17], [t18].[Address27], [t18].[City7], [t18].[StateCode7], [t18].[PostalCode7], [t18].[AddressID8] AS [Id8], [t18].[CreatedByUserId8] AS [CreatedByUserId8], [t18].[LastModifiedByUserId8] AS [LastModifiedByUserId8], [t18].[IsActive8], [t18].[IsLocked8], [t18].[CreationDate8], [t18].[LastModificationDate8], [t18].[test9], [t18].[BusinessName8], [t18].[Address18], [t18].[Address28], [t18].[City8], [t18].[StateCode8], [t18].[PostalCode8], [t18].[AddressID9] AS [Id9], [t18].[CreatedByUserId9] AS [CreatedByUserId9], [t18].[LastModifiedByUserId9] AS [LastModifiedByUserId9], [t18].[IsActive9], [t18].[IsLocked9], [t18].[CreationDate9], [t18].[LastModificationDate9], [t0].[UserID] AS [Id10], [t0].[CreatedByUserId] AS [CreatedByUserId10], [t0].[LastModifiedByUserId] AS [LastModifiedByUserId10], [t0].[IsActive] AS [IsActive10], [t0].[IsLocked] AS [IsLocked10], [t0].[CreationDate] AS [CreationDate10], [t0].[LastModificationDate] AS [LastModificationDate10]
FROM [Users] AS [t0]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t1].[CompanyTypeID], [t1].[Name], [t1].[CompanyGuid], [t1].[CompanyID], [t1].[AddressID], [t3].[test] AS [test2], [t3].[BusinessName], [t3].[Address1], [t3].[Address2], [t3].[City], [t3].[StateCode], [t3].[PostalCode], [t3].[AddressID] AS [AddressID2], [t3].[CreatedByUserId], [t3].[LastModifiedByUserId], [t3].[IsActive], [t3].[IsLocked], [t3].[CreationDate], [t3].[LastModificationDate], [t1].[CreatedByUserId] AS [CreatedByUserId2], [t1].[LastModifiedByUserId] AS [LastModifiedByUserId2], [t1].[IsActive] AS [IsActive2], [t1].[IsLocked] AS [IsLocked2], [t1].[CreationDate] AS [CreationDate2], [t1].[LastModificationDate] AS [LastModificationDate2], [t5].[test] AS [test3], [t5].[BusinessName] AS [BusinessName2], [t5].[Address1] AS [Address12], [t5].[Address2] AS [Address22], [t5].[City] AS [City2], [t5].[StateCode] AS [StateCode2], [t5].[PostalCode] AS [PostalCode2], [t5].[AddressID] AS [AddressID3], [t5].[CreatedByUserId] AS [CreatedByUserId3], [t5].[LastModifiedByUserId] AS [LastModifiedByUserId3], [t5].[IsActive] AS [IsActive3], [t5].[IsLocked] AS [IsLocked3], [t5].[CreationDate] AS [CreationDate3], [t5].[LastModificationDate] AS [LastModificationDate3], [t7].[test] AS [test4], [t7].[BusinessName] AS [BusinessName3], [t7].[Address1] AS [Address13], [t7].[Address2] AS [Address23], [t7].[City] AS [City3], [t7].[StateCode] AS [StateCode3], [t7].[PostalCode] AS [PostalCode3], [t7].[AddressID] AS [AddressID4], [t7].[CreatedByUserId] AS [CreatedByUserId4], [t7].[LastModifiedByUserId] AS [LastModifiedByUserId4], [t7].[IsActive] AS [IsActive4], [t7].[IsLocked] AS [IsLocked4], [t7].[CreationDate] AS [CreationDate4], [t7].[LastModificationDate] AS [LastModificationDate4], [t9].[test] AS [test5], [t9].[BusinessName] AS [BusinessName4], [t9].[Address1] AS [Address14], [t9].[Address2] AS [Address24], [t9].[City] AS [City4], [t9].[StateCode] AS [StateCode4], [t9].[PostalCode] AS [PostalCode4], [t9].[AddressID] AS [AddressID5], [t9].[CreatedByUserId] AS [CreatedByUserId5], [t9].[LastModifiedByUserId] AS [LastModifiedByUserId5], [t9].[IsActive] AS [IsActive5], [t9].[IsLocked] AS [IsLocked5], [t9].[CreationDate] AS [CreationDate5], [t9].[LastModificationDate] AS [LastModificationDate5], [t11].[test] AS [test6], [t11].[BusinessName] AS [BusinessName5], [t11].[Address1] AS [Address15], [t11].[Address2] AS [Address25], [t11].[City] AS [City5], [t11].[StateCode] AS [StateCode5], [t11].[PostalCode] AS [PostalCode5], [t11].[AddressID] AS [AddressID6], [t11].[CreatedByUserId] AS [CreatedByUserId6], [t11].[LastModifiedByUserId] AS [LastModifiedByUserId6], [t11].[IsActive] AS [IsActive6], [t11].[IsLocked] AS [IsLocked6], [t11].[CreationDate] AS [CreationDate6], [t11].[LastModificationDate] AS [LastModificationDate6], [t13].[test] AS [test7], [t13].[BusinessName] AS [BusinessName6], [t13].[Address1] AS [Address16], [t13].[Address2] AS [Address26], [t13].[City] AS [City6], [t13].[StateCode] AS [StateCode6], [t13].[PostalCode] AS [PostalCode6], [t13].[AddressID] AS [AddressID7], [t13].[CreatedByUserId] AS [CreatedByUserId7], [t13].[LastModifiedByUserId] AS [LastModifiedByUserId7], [t13].[IsActive] AS [IsActive7], [t13].[IsLocked] AS [IsLocked7], [t13].[CreationDate] AS [CreationDate7], [t13].[LastModificationDate] AS [LastModificationDate7], [t15].[test] AS [test8], [t15].[BusinessName] AS [BusinessName7], [t15].[Address1] AS [Address17], [t15].[Address2] AS [Address27], [t15].[City] AS [City7], [t15].[StateCode] AS [StateCode7], [t15].[PostalCode] AS [PostalCode7], [t15].[AddressID] AS [AddressID8], [t15].[CreatedByUserId] AS [CreatedByUserId8], [t15].[LastModifiedByUserId] AS [LastModifiedByUserId8], [t15].[IsActive] AS [IsActive8], [t15].[IsLocked] AS [IsLocked8], [t15].[CreationDate] AS [CreationDate8], [t15].[LastModificationDate] AS [LastModificationDate8], [t17].[test] AS [test9], [t17].[BusinessName] AS [BusinessName8], [t17].[Address1] AS [Address18], [t17].[Address2] AS [Address28], [t17].[City] AS [City8], [t17].[StateCode] AS [StateCode8], [t17].[PostalCode] AS [PostalCode8], [t17].[AddressID] AS [AddressID9], [t17].[CreatedByUserId] AS [CreatedByUserId9], [t17].[LastModifiedByUserId] AS [LastModifiedByUserId9], [t17].[IsActive] AS [IsActive9], [t17].[IsLocked] AS [IsLocked9], [t17].[CreationDate] AS [CreationDate9], [t17].[LastModificationDate] AS [LastModificationDate9]
FROM [Companys] AS [t1]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t2].[BusinessName], [t2].[Address1], [t2].[Address2], [t2].[City], [t2].[StateCode], [t2].[PostalCode], [t2].[AddressID], [t2].[CreatedByUserId], [t2].[LastModifiedByUserId], [t2].[IsActive], [t2].[IsLocked], [t2].[CreationDate], [t2].[LastModificationDate]
FROM [Addresses] AS [t2]
) AS [t3] ON [t3].[AddressID] = [t1].[AddressID]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t4].[BusinessName], [t4].[Address1], [t4].[Address2], [t4].[City], [t4].[StateCode], [t4].[PostalCode], [t4].[AddressID], [t4].[CreatedByUserId], [t4].[LastModifiedByUserId], [t4].[IsActive], [t4].[IsLocked], [t4].[CreationDate], [t4].[LastModificationDate]
FROM [Addresses] AS [t4]
) AS [t5] ON [t5].[AddressID] = [t1].[AddressID]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t6].[BusinessName], [t6].[Address1], [t6].[Address2], [t6].[City], [t6].[StateCode], [t6].[PostalCode], [t6].[AddressID], [t6].[CreatedByUserId], [t6].[LastModifiedByUserId], [t6].[IsActive], [t6].[IsLocked], [t6].[CreationDate], [t6].[LastModificationDate]
FROM [Addresses] AS [t6]
) AS [t7] ON [t7].[AddressID] = [t1].[AddressID]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t8].[BusinessName], [t8].[Address1], [t8].[Address2], [t8].[City], [t8].[StateCode], [t8].[PostalCode], [t8].[AddressID], [t8].[CreatedByUserId], [t8].[LastModifiedByUserId], [t8].[IsActive], [t8].[IsLocked], [t8].[CreationDate], [t8].[LastModificationDate]
FROM [Addresses] AS [t8]
) AS [t9] ON [t9].[AddressID] = [t1].[AddressID]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t10].[BusinessName], [t10].[Address1], [t10].[Address2], [t10].[City], [t10].[StateCode], [t10].[PostalCode], [t10].[AddressID], [t10].[CreatedByUserId], [t10].[LastModifiedByUserId], [t10].[IsActive], [t10].[IsLocked], [t10].[CreationDate], [t10].[LastModificationDate]
FROM [Addresses] AS [t10]
) AS [t11] ON [t11].[AddressID] = [t1].[AddressID]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t12].[BusinessName], [t12].[Address1], [t12].[Address2], [t12].[City], [t12].[StateCode], [t12].[PostalCode], [t12].[AddressID], [t12].[CreatedByUserId], [t12].[LastModifiedByUserId], [t12].[IsActive], [t12].[IsLocked], [t12].[CreationDate], [t12].[LastModificationDate]
FROM [Addresses] AS [t12]
) AS [t13] ON [t13].[AddressID] = [t1].[AddressID]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t14].[BusinessName], [t14].[Address1], [t14].[Address2], [t14].[City], [t14].[StateCode], [t14].[PostalCode], [t14].[AddressID], [t14].[CreatedByUserId], [t14].[LastModifiedByUserId], [t14].[IsActive], [t14].[IsLocked], [t14].[CreationDate], [t14].[LastModificationDate]
FROM [Addresses] AS [t14]
) AS [t15] ON [t15].[AddressID] = [t1].[AddressID]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t16].[BusinessName], [t16].[Address1], [t16].[Address2], [t16].[City], [t16].[StateCode], [t16].[PostalCode], [t16].[AddressID], [t16].[CreatedByUserId], [t16].[LastModifiedByUserId], [t16].[IsActive], [t16].[IsLocked], [t16].[CreationDate], [t16].[LastModificationDate]
FROM [Addresses] AS [t16]
) AS [t17] ON [t17].[AddressID] = [t1].[AddressID]
) AS [t18] ON [t18].[CompanyID] = [t0].[CompanyID]
WHERE ([t18].[CompanyTypeID] = @p0) OR ([t18].[CompanyTypeID] = @p1)