The multi-part identifier "XXX" could not be bound. after Take() in EF Core 3.1.0 #19947
Assignees
Labels
area-query
closed-fixed
The issue has been fixed and is/will be included in the release indicated by the issue milestone.
customer-reported
punted-for-5.0
type-bug
Milestone
Comments
|
I fix it by but Take(12) after Select |
MhozaifaA
changed the title
MhozaifaA
changed the title
|
possible dupe of #19763 |
|
Does it work in master? |
|
@maumar to try on master. |
|
the query will execute fine when fix aliases in SqlServer >>>> but in normal "Take" still make issue , not working as expected to master |
|
.... any way thanks .. it's not big issue as i see .. and in default linq work after collection .... hope to solve it in EFCore mapping & fix aliases |
|
@MhozaifaA can you provide code listing for all the entities used in the query as well contents of OnModelCreating method on your DbContext? |
|
@maumar .... Maybe it could be useful.... sorry for being late for the answer users Table public class EUser : IdentityUser<int>
{
// [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
#region - Constructor -
public EUser() // null
{
Areas = new HashSet<Area>();
Documents = new HashSet<Document>();
Cars = new HashSet<Car>();
Reports = new HashSet<Report>();
Discounts = new HashSet<Discount>();
Invites = new HashSet<Invite>();
InvitedInvites = new HashSet<Invite>();
Taxis = new HashSet<Taxi>();
APIBoxs = new HashSet<Box>();
Boxs = new HashSet<Box>();
Stops= new HashSet<Stop>();
Sallaries = new HashSet<Sallary>();
}
#endregion
#region - BaseEntity -
public DateTimeOffset? DateDeleted { get; set; }
public bool IsDeleted => DateDeleted.HasValue;
#endregion
#region - API -
//
[Column(TypeName = "int")]
public int PolicyType { get; set; } // enum PolicyTypes
[InverseProperty(nameof(Box.APIEUser))]
public ICollection<Box> APIBoxs { get; set; }
#endregion
#region - Share -
[Column(TypeName = "int")]
[DefaultValue(0)]
public int TypeAccount { get; set; } // 0 API , 1 Captain , 2 User
[Column(TypeName = "nvarchar(128)")] // GUID 128
public string SerialNumber { get; set; } //
//[Required]
[Column(TypeName = "nvarchar(100)")]
public string FullName { get; set; } //
[Column(TypeName = "nvarchar(50)")]
public string SureName { get; set; } //
//[Required]
[Column(TypeName = "varchar(20)")]
// [Index()]// warrning..should be unique
public string MobileNumber { get; set; } //
[Column(TypeName = "varchar(50)")]
public string NationalNumber { get; set; } //
// datetime 1753yers to 9999yers
[Column(TypeName = "datetime2")] // here can set from "0001 / 01 / 01" to "9999 / 12 / 31"
public Nullable<DateTime> DateOfBirth { get; set; } //
[Column(TypeName = "datetime")]
public Nullable<DateTime> DateRegister { get; set; } //
[Column(TypeName = "datetime")]
public Nullable<DateTime> LastSeen { get; set; } //
[Column(TypeName = "nvarchar(max)")]
public string Note { get; set; }
public ICollection<Area> Areas { get; set; }
public ICollection<Document> Documents { get; set; }
public ICollection<Report> Reports { get; set; }
[InverseProperty(nameof(Box.EUser))]
public ICollection<Box> Boxs { get; set; }
#endregion
#region - Captain -
[Column(TypeName = "nvarchar(max)")]
public string AddressHome { get; set; }//
//[Column(TypeName = "float")]
//public Nullable<double> Salary { get; set; } //
[Column(TypeName = "varchar(20)")]
public string PercentTaxi { get; set; } //
[Column(TypeName = "float")]
public Nullable<double> InsuranceValue { get; set; } // Token
[Column(TypeName = "varchar(max)")]
public string LastLatLong { get; set; }
// [Column(TypeName = "decimal(max)")]
// public DbGeography LastLatLong { get; set; } // enough DECIMAL type
//[Column(TypeName = "float")]
//public decimal Latitude { get; set; }
//[Column(TypeName = "float")]
//public float Longitude { get; set; }
// https://www.movable-type.co.uk/scripts/latlong-db.html
//The cosine law is: d = acos(sin(φ1)⋅sin(φ2) + cos(φ1)⋅cos(φ2)⋅cos(Δλ) ) ⋅ R
// where: φ = latitude, λ = longitude(in radians)
// R = radius of earth
// d = distance between the points(in same units as R)
//[Column(TypeName = "varchar(20)")]
//public string NumJob { get; set; } //
//[Column(TypeName = "datetime")]
//public Nullable<DateTime> DateJob { get; set; } //
//[Column(TypeName = "bit")]
//public bool IsJob { get; set; } // 0 done 1
[Column(TypeName = "bit")]
public bool IsActivated { get; set; } //
//[Column(TypeName = "int")]
//public int CityWork { get; set; } // from Enum.Cities
public Nullable<int> CityId { get; set; } //CityWork
public City City { get; set; }
public ICollection<Car> Cars { get; set; }
public ICollection<Stop> Stops { get; set; }
public ICollection<Sallary> Sallaries { get; set; }
//
//[InverseProperty(nameof(Taxi.CaptainEUser))]
//public ICollection<Taxi> CaptainTaxis { get; set; }
#endregion
#region - user -
[Column(TypeName = "bit")]
public bool IsBlocked { get; set; }
public ICollection<Discount> Discounts { get; set; }
[InverseProperty(nameof(Invite.EUser))]
public ICollection<Invite> Invites { get; set; }
[InverseProperty(nameof(Invite.InvitedEUser))]
public ICollection<Invite> InvitedInvites { get; set; }
public ICollection<Taxi> Taxis { get; set; }
#endregion
}Taxi Table public class Taxi : BaseEntity
{
[Column(TypeName ="nvarchar(50)")]
[Required]
public string Number { get; set; }
[Column(TypeName = "datetime")]
public DateTime DateRequest { get; set; }
[Column(TypeName = "datetime")]
public Nullable<DateTime> DateCaptainArrived { get; set; }
[Column(TypeName = "datetime")]
public Nullable<DateTime> DateArrived { get; set; }
[Column(TypeName = "datetime")]
public Nullable<DateTime> DateBooked { get; set; }
[Column(TypeName = "int")]
public int TaxiType { get; set; } // enum TaxiTypes
[Column(TypeName = "float")]
public Nullable<double> StartLatitude { get; set; }
[Column(TypeName = "float")]
public Nullable<double> StartLongitude { get; set; }
[Column(TypeName = "float")]
public Nullable<double> EndLatitude { get; set; }
[Column(TypeName = "float")]
public Nullable<double> EndLongitude { get; set; }
[Column(TypeName = "nvarchar(Max)")]
public string LocalFrom { get; set; }
[Column(TypeName = "nvarchar(Max)")]
public string LocalDestination { get; set; }
[Column(TypeName = "bigint")]
public Nullable<long> Time { get; set; }
[Column(TypeName = "float")]
public Nullable<double> Distance { get; set; }
[Column(TypeName = "int")]
public Nullable<int> UserRate { get; set; }
[Column(TypeName = "nvarchar(max)")]
public string UserTextRate { get; set; }
[Column(TypeName = "int")]
public Nullable<int> CaptainRate { get; set; }
[Column(TypeName = "nvarchar(max)")]
public string CaptainTextRate { get; set; }
[Column(TypeName = "float")]
public Nullable<double> Value { get; set; }
//
[Column(TypeName = "float")]
public Nullable<double> RestValue { get; set; } //
//
//
[Column(TypeName = "float")]
public Nullable<double> BagValue { get; set; } //
//
//
[Column(TypeName = "float")]
public Nullable<double> Tax { get; set; } //
[Column(TypeName = "nvarchar(max)")]
public string DisableText { get; set; }//
[Column(TypeName = "nvarchar(max)")]
public string ReportText { get; set; } //
[Column(TypeName = "bit")]
public bool IsReportDeleted { get; set; } //
[Column(TypeName = "varchar(20)")]
public string PercentTaxi { get; set; }//
public Nullable<int> DiscountId { get; set; }
public Discount Discount { get; set; }
[ForeignKey(nameof(UserEUser)), Column(Order = 1)]
public int UserEUserId { get; set; }
public EUser UserEUser { get; set; } //
public Nullable<int> CarId { get; set; }
public Car Car { get; set; } //
//[ForeignKey(nameof(CaptainEUser)), Column(Order = 2)]
//public Nullable<int> CaptainEUserId { get; set; }
//public EUser CaptainEUser { get; set; } //
public Nullable<int> BoxId { get; set; }
public Box Box { get; set; } //
public double CatchValue => Value ?? 0 - BagValue ?? 0 +
( RestValue != null ? (RestValue.Value < 0 ? 0 : RestValue.Value) : 0 ) - Distance ?? 0 + Tax ?? 0;
}Car Table public class Car :BaseEntity
{
public Car()
{
Documents = new HashSet<Document>();
Taxis = new HashSet<Taxi>();
}
[Required]
[Column(TypeName = "nvarchar(50)")]
public string LabelCar { get; set; }
[Column(TypeName = "int")]
public int CarLicenceType { get; set; } //
[Column(TypeName = "datetime")]
public Nullable<DateTime> DateOutCarLicence { get; set; } //
[Column(TypeName = "datetime")]
public Nullable<DateTime> DateOutCarSecurta { get; set; } //
[Column(TypeName = "nvarchar(50)")]
public string NumCard { get; set; }//
[Column(TypeName = "char(9)")]
//[DefaultValue("FFFFFF")]
public string Color { get; set; }
[Column(TypeName = "nvarchar(max)")]
public string Note { get; set; }
public int EUserId { get; set; }
public EUser EUser { get; set; }
public int CarModelId { get; set; }
public CarModel CarModel { get; set; }
public int CarTypeId { get; set; }
public CarType CarType { get; set; }
public ICollection<Document> Documents { get; set; }
public ICollection<Taxi> Taxis { get; set; }
}
public class ElCaptainDbContext : IdentityDbContext<EUser, ERole, int, EUserClaim, EUserRole
, EUserLogin, ERoleClaim, EUserToken>
{
#region - Constructor -
public ElCaptainDbContext(DbContextOptions<ElCaptainDbContext> options) : base(options)
{
}
#endregion
#region - Main -
public DbSet<Area> Areas { get; set; }
public DbSet<Car> Cars { get; set; }
public DbSet<CarModel> CarModels { get; set; }
public DbSet<Document> Documents { get; set; }
public DbSet<Invite> Invites { get; set; }
public DbSet<Report> Reports { get; set; }
public DbSet<Stop> Stops { get; set; }
public DbSet<Sallary> Sallaries { get; set; }
public DbSet<Taxi> Taxis { get; set; }
#endregion
#region - Setting -
public DbSet<Ad> Ads { get; set; }
public DbSet<Box> Boxes { get; set; }
public DbSet<CarType> CarTypes { get; set; }
public DbSet<City> Cities { get; set; }
public DbSet<Discount> Discounts { get; set; }
public DbSet<Job> Jobs { get; set; }
public DbSet<Notification> Notifications { get; set; }
public DbSet<Setting> Settings { get; set; }
public DbSet<Tip> Tips { get; set; }
#endregion
//protected override void OnModelCreating(ModelBuilder modelBuilder)
//{
// modelBuilder.Entity<ERole>().HasData(
// new ERole { Id=1,Name="Admin"}
// );
// base.OnModelCreating(modelBuilder);
//}
}I am sorry if my code was not professional |
|
@MhozaifaA no worries. With the additional info you provided I was able to reproduce this issue. |
|
Simplified repro: [ConditionalFact]
public virtual void Repro19947()
{
using var ctx = new ElCaptainDbContext();
ctx.Database.EnsureDeleted();
ctx.Database.EnsureCreated();
var query = ctx.Users.Select(captain => new
{
CaptainRateDtos = captain.Cars
.SelectMany(car0 => car0.Taxis)
.OrderByDescending(taxi => taxi.DateArrived).Take(12)
.Select(taxi => new
{
Rate = taxi.UserRate.Value,
UserRateText = taxi.UserTextRate,
UserId = taxi.UserEUser.Id,
}).ToList(),
ReportCount = captain.Cars
.SelectMany(car1 => car1.Taxis).Count(taxi0 => taxi0.ReportText != ""),
}).SingleOrDefault();
}
public class EUser
{
public int Id { get; set; }
public ICollection<Car> Cars { get; set; }
}
public class Taxi
{
public int Id { get; set; }
public DateTime? DateArrived { get; set; }
public int? UserRate { get; set; }
public string UserTextRate { get; set; }
public string ReportText { get; set; }
public EUser UserEUser { get; set; }
}
public class Car
{
public int Id { get; set; }
public ICollection<Taxi> Taxis { get; set; }
}
public class ElCaptainDbContext : DbContext
{
public DbSet<EUser> Users { get; set; }
public DbSet<Car> Cars { get; set; }
public DbSet<Taxi> Taxis { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Repro19947;Trusted_Connection=True;MultipleActiveResultSets=true");
}
}generated sql: SELECT [t0].[c], [t0].[Id], [t3].[UserRate], [t3].[UserTextRate], [t3].[Id], [t3].[Id0], [t3].[Id00]
FROM (
SELECT TOP(2) (
SELECT COUNT(*)
FROM [Cars] AS [c]
INNER JOIN [Taxis] AS [t] ON [c].[Id] = [t].[CarId]
WHERE ([u].[Id] = [c].[EUserId]) AND (([t].[ReportText] <> N'') OR [t].[ReportText] IS NULL)) AS [c], [u].[Id]
FROM [Users] AS [u]
) AS [t0]
OUTER APPLY (
SELECT [t].[UserRate], [t].[UserTextRate], [u0].[Id], [t].[Id] AS [Id0], [t].[Id0] AS [Id00], [t].[DateArrived]
FROM (
SELECT TOP(12) [c0].[Id], [c0].[EUserId], [t1].[Id] AS [Id0], [t1].[CarId], [t1].[DateArrived], [t1].[ReportText], [t1].[UserEUserId], [t1].[UserRate], [t1].[UserTextRate]
FROM [Cars] AS [c0]
INNER JOIN [Taxis] AS [t1] ON [c0].[Id] = [t1].[CarId]
WHERE [t0].[Id] = [c0].[EUserId]
ORDER BY [t1].[DateArrived] DESC
) AS [t2]
LEFT JOIN [Users] AS [u0] ON [t].[UserEUserId] = [u0].[Id]
) AS [t3]
ORDER BY [t0].[Id], [t3].[DateArrived] DESC, [t3].[Id0], [t3].[Id00]Sql exception: if Take call is removed, everything works fine, generating the following: SELECT [t0].[c], [t0].[Id], [t2].[UserRate], [t2].[UserTextRate], [t2].[Id], [t2].[Id0], [t2].[Id1]
FROM (
SELECT TOP(2) (
SELECT COUNT(*)
FROM [Cars] AS [c]
INNER JOIN [Taxis] AS [t] ON [c].[Id] = [t].[CarId]
WHERE ([u].[Id] = [c].[EUserId]) AND (([t].[ReportText] <> N'') OR [t].[ReportText] IS NULL)) AS [c], [u].[Id]
FROM [Users] AS [u]
) AS [t0]
LEFT JOIN (
SELECT [t1].[UserRate], [t1].[UserTextRate], [u0].[Id], [c0].[Id] AS [Id0], [t1].[Id] AS [Id1], [t1].[DateArrived], [c0].[EUserId]
FROM [Cars] AS [c0]
INNER JOIN [Taxis] AS [t1] ON [c0].[Id] = [t1].[CarId]
LEFT JOIN [Users] AS [u0] ON [t1].[UserEUserId] = [u0].[Id]
) AS [t2] ON [t0].[Id] = [t2].[EUserId]
ORDER BY [t0].[Id], [t2].[DateArrived] DESC, [t2].[Id0], [t2].[Id1]if projection 'ReportCount' is removed we generate the following: SELECT [t].[Id], [t2].[UserRate], [t2].[UserTextRate], [t2].[Id], [t2].[Id0], [t2].[Id00]
FROM (
SELECT TOP(2) [u].[Id]
FROM [Users] AS [u]
) AS [t]
OUTER APPLY (
SELECT [t].[UserRate], [t].[UserTextRate], [u0].[Id], [t].[Id] AS [Id0], [t].[Id0] AS [Id00], [t].[DateArrived]
FROM (
SELECT TOP(12) [c].[Id], [c].[EUserId], [t0].[Id] AS [Id0], [t0].[CarId], [t0].[DateArrived], [t0].[ReportText], [t0].[UserEUserId], [t0].[UserRate], [t0].[UserTextRate]
FROM [Cars] AS [c]
INNER JOIN [Taxis] AS [t0] ON [c].[Id] = [t0].[CarId]
WHERE [t].[Id] = [c].[EUserId]
ORDER BY [t0].[DateArrived] DESC
) AS [t1]
LEFT JOIN [Users] AS [u0] ON [t].[UserEUserId] = [u0].[Id]
) AS [t2]
ORDER BY [t].[Id], [t2].[DateArrived] DESC, [t2].[Id0], [t2].[Id00]which throws different error (invalid column) |
smitpatel
added
the
closed-fixed
smitpatel
added a commit
that referenced
this issue
Mar 24, 2021
- Don't apply Include on entities with Include already applied - Update table references when pushing down select into left for set operation - Update identifiers after applying set operation if the projection removed exiting identifiers - Update SQL references in pending collection during push down Fix for the repro in #17337 Resolves #18738 Resolves #19763 Resolves #19947 Resolves #20813 Resolves #21026 Resolves #22222 Resolves #23676 Resolves #23720
smitpatel
added a commit
that referenced
this issue
Mar 24, 2021
- Don't apply Include on entities with Include already applied - Update table references when pushing down select into left for set operation - Update identifiers after applying set operation if the projection removed exiting identifiers - Update SQL references in pending collection during push down Fix for the repro in #17337 Resolves #18738 Resolves #19763 Resolves #19947 Resolves #20813 Resolves #21026 Resolves #22222 Resolves #23676 Resolves #23720 Resolves #24216
smitpatel
added a commit
that referenced
this issue
Mar 24, 2021
- Don't apply Include on entities with Include already applied - Update table references when pushing down select into left for set operation - Update identifiers after applying set operation if the projection removed exiting identifiers - Update SQL references in pending collection during push down Fix for the repro in #17337 Resolves #18738 Resolves #19763 Resolves #19947 Resolves #20813 Resolves #21026 Resolves #22222 Resolves #23676 Resolves #23720 Resolves #24216
smitpatel
added a commit
that referenced
this issue
Mar 25, 2021
- Don't apply Include on entities with Include already applied - Update table references when pushing down select into left for set operation - Update identifiers after applying set operation if the projection removed exiting identifiers - Update SQL references in pending collection during push down Fix for the repro in #17337 Resolves #18738 Resolves #19763 Resolves #19947 Resolves #20813 Resolves #21026 Resolves #22222 Resolves #23676 Resolves #23720 Resolves #24216
smitpatel
added a commit
that referenced
this issue
Mar 25, 2021
- Don't apply Include on entities with Include already applied - Update table references when pushing down select into left for set operation - Update identifiers after applying set operation if the projection removed exiting identifiers - Update SQL references in pending collection during push down Fix for the repro in #17337 Resolves #18738 Resolves #19763 Resolves #19947 Resolves #20813 Resolves #21026 Resolves #22222 Resolves #23676 Resolves #23720 Resolves #24216
|
@ajcvickers is there any chance we get this fix for netcore 3.1 ? (package efcore 5.0 or 3.1.14) |
|
@smitpatel Can you confirm that this is too risky for a patch release? @h0s See the planning process for information on when we patch. |
|
Yes, it is too risky for patch and also involves API breaks. |
|
... so even though 3.1 is in LTS, this is not going to be fixed? Are we being forced to upgrade to 6.0 to get this working? |
|
@jeremy-holovacs-sp That's correct. The fix is too risky to include in a patch. See release planning for more information. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
i saw this issue fixed ... but i still it in EF Core 3.1.0
The following code :
Exception:
The multi-part identifier "t.UserEUserId" could not be bound.
The multi-part identifier "t.UserRate" could not be bound.
The multi-part identifier "t.UserTextRate" could not be bound.
The multi-part identifier "t.DateArrived" could not be bound.
The multi-part identifier "t.DateArrived" could not be bound.
The multi-part identifier "t.DateCaptainArrived" could not be bound.
The multi-part identifier "t.DateCaptainArrived" could not be bound.
The multi-part identifier "t.DateRequest" could not be bound.
The multi-part identifier "t.Id0" could not be bound.
The multi-part identifier "t.Id" could not be bound.
The multi-part identifier "t.c" could not be bound.
The Sql :
The whole issue with CaptainRateDtos =aptain.Cars.Whe........
EF Core version: 3.1.0
Database provider: (e.g. Microsoft.EntityFrameworkCore.SqlServer)
Target framework: (e.g. .NET Core 3.1)
Operating system:
IDE: (e.g. Visual Studio 2019 16.4.2)
The text was updated successfully, but these errors were encountered: