I have the following linq-query (false and true is because of dynamically creating the query):
(from x in ((ObjectQuery<PersonView>)PersonView).MergeAs(0)
where x.Info && (((False || (True && (x.Person.FirstName == compareValue))) || (True && (x.Person.FirstName == compareValue))) || (True && ((x.Person.FirstName == "") || (x.Person.FirstName == null))))
where (x.DateTime >= startDate.Date) && (x.DateTime <= endDate.Date)
select x.PersonID).Distinct()
And it translates to this sql query:
SELECT
[Distinct1].[PersonID] AS [PersonID]
FROM ( SELECT DISTINCT
[Extent1].[PersonID] AS [PersonID]
FROM (SELECT
[PersonView].[PersonID] AS [PersonID],
[PersonView].[DateTime] AS [DateTime],
[PersonView].[Info] AS [Info]
FROM [core].[PersonView] AS [PersonView]) AS [Extent1]
LEFT OUTER JOIN [core].[Persons] AS [Extent2] ON [Extent1].[PersonID] = [Extent2].[PersonID]
WHERE ([Extent1].[Info] = 1) AND ([Extent2].[FirstName] IN (@p__linq__0,@p__linq__1,N'') OR [Extent2].[FirstName] IS NULL) AND ([Extent1].[DateTime] >= @p__linq__2) AND ([Extent1].[DateTime] <= @p__linq__3)
) AS [Distinct1]
My goal is to replace the left join with an inner join because of performance. I have been searching and I have read that if the property is not nullable (person in this case is not nullable) it should be translated to an inner join. Which is obviously not the case here. How can I make Entity Framework generate an inner join?
EDIT: Here it is explained that a required assocation should translate to an inner join. What makes it a required association? In my case the foreign key is not nullable.
I work database first. This is what is generated:
public partial class PersonView: DbEntity
{
private int _personID;
private System.DateTime _dateTime;
private bool _info;
public int PersonID { get{ return _personID;} set{ _personID = value; OnPropertyChanged("PersonID");} }
public System.DateTime DateTime { get{ return _dateTime;} set{ _dateTime = value; OnPropertyChanged("DateTime");} }
public bool Info{ get{ return _info;} set{ _info = value; OnPropertyChanged("Info");} }
public virtual Person Person { get; set; }
}
I tried adding metadata but it makes no difference.
[MetadataType(typeof(PersonViewMetaData))]
public partial class PersonView
{ }
public sealed class PersonViewMetaData
{
[Required]
public Person Person { get; set; }
}