LINQ To SQL Weird Join Issue
I have a simple database with two tables. Users and configurations. The user has a foreign key to associate with a specific configuration.
I'm having a weird problem where the following request always causes an inner join in the config table regardless of the value of the second parameter. As far as I can tell, even though the initialization part of the UserConfiguration = object is conditional, LINQ doesn't see this and determines that the relationship is respected anyway.
If I do remove the last initialization, everything works as expected. It is not an inner connection when loadConfiguration == false, and it is connected when loadConfiguration == true.
Anyone have any ideas about this? Does this syntax just not work? The only thought I have now is to wrap the return in a basic if statement - I just wanted to avoid duplicate lines.
public UserAccount GetByUsername(string username, bool loadConfiguration)
{
using (Database database = new Database())
{
if (loadConfiguration)
{
DataLoadOptions loadOptions = new DataLoadOptions();
loadOptions.LoadWith<User>(c => c.Configuration);
database.LoadOptions = loadOptions;
}
return (from c in database.Users
where c.Username == username
select new UserAccount
{
ID = c.ID,
ConfigurationID = c.ConfigurationID,
Username = c.Username,
Password = c.Password.ToArray(),
HashSalt = c.HashSalt,
FirstName = c.FirstName,
LastName = c.LastName,
EmailAddress = c.EmailAddress,
UserConfiguration = (loadConfiguration) ? new ApplicationConfiguration
{
ID = c.Configuration.ID,
MonthlyAccountPrice = c.Configuration.MonthlyAccountPrice,
TrialAccountDays = c.Configuration.TrialAccountDays,
VAT = c.Configuration.VAT,
DateCreated = c.Configuration.DateCreated
} : null
}).Single();
}
}
Thanks in advance,
Martin.
No, it won't work. I have faced similar problems many times. The reason for this has to do with expressions at compile time versus conditions at run time.
You can make 2 requests or if you don't mind joining the config regardless of the loadConfiguration parameter, you can use:
var q = (from c in database.Users
where c.Username == username
select c).Single();
and then use the Linq-to-Objects result.
source to share
Replace .Single () with SingleOrDefault () and Linq will switch to the left outer join. I don't know if this will do it in your case, but in some cases it does.
To change dint see the "Unit" section for the whole request, not the config part:
try this:
UserConfiguration = (loadConfiguration && c.Configuration != null) ? new ApplicationConfiguration
{
ID = c.Configuration.ID,
MonthlyAccountPrice = c.Configuration.MonthlyAccountPrice,
TrialAccountDays = c.Configuration.TrialAccountDays,
VAT = c.Configuration.VAT,
DateCreated = c.Configuration.DateCreated
} : null
source to share