Associations with ExpressionPredicate are lost when query is joined #1301
Description
The test entity:
[Table(Name = User.TableName)]
public class User : EntityBase
{
internal const string TableName = "Users";
[PrimaryKey, Identity]
[Column(Name = "Id")]
public override int Id { get; set; }
[Column(Name = "Name")]
public string Name { get; set; }
[Column(Name = "IsActive")]
public bool IsActive { get; set; }
[Column(Name = "Guid")]
public Guid Guid { get; set; }
[Column(Name = "AppointmentId")]
public int? AppointmentId { get; set; }
[Association(ThisKey = "AppointmentId", OtherKey = "Id", CanBeNull = true, Relationship = Relationship.ManyToOne,
KeyName = "FK_Users_AppointmentId",
BackReferenceName = "Users")]
public virtual Appointment Appointment { get; set; }
[Column]
public int? ParentUserId { get; set; }
[Association(CanBeNull = true, ExpressionPredicate = nameof(ParentPredicate))]
public User Parent { get; set; }
public static Expression<Func<User, User, bool>> ParentPredicate => (u1, u2) =>
u1.ParentUserId == u2.Id && u2.IsActive;
}
Test methods
[Test]
public void Linq2DBOtherTests_Bug_With_Expression_Predicate_OK()
{
using (var db = GetDataConnection())
{
var q123 = from x in db.GetTable<User>()
select new
{
x.Id,
ParentName = x.Parent.Name,
AppointmentName = x.Appointment.Name
};
q123.ToList();
}
}
[Test]
public void Linq2DBOtherTests_Bug_With_Expression_Predicate_Fail()
{
using (var db = GetDataConnection())
{
// something to join. Anything. Let's say, that here is id selection from some permission view
var p1 = db.Users.Where(x => x.Id == 123);
var q123 = from x in db.GetTable<User>()
join p in p1 on x.Id equals p.Id
select new
{
x.Id,
ParentName = x.Parent.Name,
AppointmentName = x.Appointment.Name
};
q123.ToList();
}
}
Without joining associations properties selection works well:
SELECT
[t3].[Id],
[t1].[Name],
[t2].[Name] as [Name1]
FROM
[Users] [t3] WITH (NOLOCK)
LEFT JOIN [Users] [t1] ON [t3].[ParentUserId] = [t1].[Id] AND 1 = [t1].[IsActive]
LEFT JOIN [Appointments] [t2] ON [t3].[AppointmentId] = [t2].[Id]
But with any join generated SQL doesn't have selection for association properties
SELECT
[x].[Id]
FROM
[Users] [x] WITH (NOLOCK)
WHERE
[x].[Id] = 123
Environment details
linq2db: 2.0.0
provider: SqlServerVersion.v2012