Exception "column reference is ambiguous" EF Core

[kenzouno1]

Description

Batch Update in EF Core with pgsql throw exception column reference "Id" is ambiguous

My Code

await db.Customers
.Where(x => lstIds.Contains(x.Id))
.UpdateAsync(x => new Customer{
     Status = CustomerStatus.Deleted
});

Sql Command

UPDATE "Customers"
SET "Status" = $1
WHERE EXISTS ( SELECT 1 FROM (SELECT x."Id", x."Address", x."AffSid", x."ApplicationUserId", x."Avatar", x."BankName", x."BankNumber", x."BigDataId", x."Code", x."CreateDate", x."Day", x."Email", x."FacebookId", x."Fax", x."FirstName", x."FullName", x."Gender", x."IdCard", x."IdCardIssued", x."IdCardProvince", x."IsRecommender", x."IsStaff", x."LastName", x."Month", x."Notes", x."Phone", x."PhoneOther", x."Rate", x."RecommenderId", x."RsWifiId", x."Status", x."SyncApiCustomerId", x."SyncApiDate", x."TaxCode", x."Year", x."ZaloId", x."Id", x."Location_DistrictId", x."Location_ProvinceId", x."Location_WardId"
FROM "Customers" AS x
WHERE x."Id" IN (1)) B
               WHERE "Customers"."Id" = B."Id"
           )

Exception

If you are seeing an exception, include the full exceptions details (message and stack trace).

Exception message: 42702: column reference "Id" is ambiguous
Stack trace: at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.cs:line 1012
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.cs:line 1032
--- End of stack trace from previous location where exception was thrown ---
   at System.Threading.Tasks.ValueTask`1.get_Result()
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming) in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:line 444
   at Npgsql.NpgsqlDataReader.NextResult() in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:line 332
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1217
   at System.Threading.Tasks.ValueTask`1.get_Result()
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1042
   at Npgsql.NpgsqlCommand.ExecuteNonQuery() in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1025
   at Z.EntityFramework.Extensions.BatchUpdate.Execute[T](IQueryable`1 query, Expression`1 updateFactory)
   at BatchUpdateExtensions.UpdateFromQuery[T](IQueryable`1 query, Expression`1 updateFactory, Action`1 batchUpdateBuilder)
   at Z.EntityFramework.Plus.BatchUpdateExtensions.Update[T](IQueryable`1 query, Expression`1 updateFactory, Action`1 batchUpdateBuilder)
   at Z.EntityFramework.Plus.BatchUpdateExtensions.<>c__DisplayClass2_0`1.<UpdateAsync>b__0()
   at System.Threading.Tasks.Task`1.InnerInvoke()
   at System.Threading.Tasks.Task.<>c.<.cctor>b__275_1(Object obj)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
--- End of stack trace from previous location where exception was thrown ---
   at Redsand.Site.Api.CustomerController.BulkRemove(String ids) in D:\ECRM-2018\Api\Customer\CustomerController.cs:line 335

Further technical details

• EF version: 2.2
• EF Plus version: 2.2.32
• Database Provider: Postgresql

[JonathanMagnan]

Thank you for reporting,

We will look at it.

Best Regards,

Jon

---

Performance Libraries context.BulkInsert(list, options => options.BatchSize = 1000); Entity Framework Extensions • Entity Framework Classic • Bulk Operations • Dapper Plus

Runtime Evaluation Eval.Execute("x + y", new {x = 1, y = 2}); // return 3 C# Eval Function • SQL Eval Function

[JonathanMagnan]

Hello @kenzouno1 ,

Could you provide a runnable code/project with this issue.

My developer tried it but didn't get a similar error;

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.EntityFrameworkCore;

namespace lab.EFCore20.PostgreSQL
{
	class Request_Update_IDDouble
	{
		public static void Execute()
		{
			bool createAndDeleteBD = true;

			if (createAndDeleteBD)
			{
				// Create BD 
				using (var context = new EntityContext())
				{
					My.DeleteBD(context);
					context.Database.EnsureCreated();
				}
			}

			// CLEAN  
			using (var context = new EntityContext())
			{
				context.EntitySimples.RemoveRange(context.EntitySimples);

				context.SaveChanges();
			}


			// SEED  
			using (var context = new EntityContext())
			{
				context.EntitySimples.Add(new EntitySimple { ColumnInt = 1 });

				context.EntitySimples.Add(new EntitySimple { ColumnInt = 2 });

				context.EntitySimples.Add(new EntitySimple { ColumnInt = 3 });

				context.SaveChanges();
			}



			// TEST  
			using (var context = new EntityContext())
			{
				List<EntitySimple> list = new List<EntitySimple>();
				list.Add(new EntitySimple { ColumnInt = 10 });
				list.Add(new EntitySimple { ColumnInt = 11 });
				list.Add(new EntitySimple { ColumnInt = 12 });

				context.BulkInsert(list);
				var listId = list.Select(x => x.ID).ToList();

				// context.EntitySimples.Where(x => listId.Contains(x.ID)).Update(x => new EntitySimple() {ColumnString = "TEST"});
				context.EntitySimples.Where(x => listId.Contains(x.ID)).UpdateFromQuery(x => new EntitySimple() {ColumnString = "TEST"});
				var testaa = context.EntitySimples.ToList();
			} 
		}

		public class EntityContext : DbContext
		{
			public EntityContext()
			{
			}

			public DbSet<EntitySimple> EntitySimples { get; set; }

			protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
			{
				optionsBuilder.UseNpgsql(My.Connection);
			}
		}

		public class EntitySimple
		{
			public int ID { get; set; }
			public int ColumnInt { get; set; }
			public String ColumnString { get; set; } 
		}
	}
}

[kenzouno1]

Exactly this not working if Model has Property type ComplexType. How to remove ComplexType Field on the query?

That is my code.

@JonathanMagnan UpdateDynamic.zip

[JonathanMagnan]

Hello @kenzouno1 ,

Just to give you an update, we successfully reproduced the issue even on SQL Server by using your code.

My developer will try something this week by explicitly selecting column or by modifying the SQL to make sure column alias are unique.

I will keep you updated

[JonathanMagnan]

Hello @kenzouno1 ,

It took us more time than expected but my developer successfully fixed the issue.

However, we only succeed to make the fix for EF Core 3.x ;(

I believe you currently use EF Core 2.x and for this version, we never found how to do it correctly since column alias are missing unlike EF Core 3.x

[kenzouno1]

I think need a feature blacklist properties. Can you do that?

[JonathanMagnan]

It depends,

Let us know more about your idea ;)

[kenzouno1]

I'm not real .Net developer so I don't know how to do it. But i think on query you need get all Properties of Model. So why not have a feature add a Black List Property Names and on query will remove it from List Properties before execute command

[VicenzoMartinelli]

Hi @JonathanMagnan, does this fix has been published for ef core 3.x? If yes, in what version?