c# - Entity Framework Core 原始 SQL 错误 : System. ArgumentException 已添加具有相同键的项目。关键:身份证

Question

我正在使用 .NET v5.0.100-preview.8.20417.9、ASP.NET Core Web API 5、Microsoft SQL Server 2019、Entity Framework Core v5.0.0-preview.8.20414.8。

数据库

CREATE TABLE [dbo].[AspNetUsers]
(
    [Id] [nvarchar](450) NOT NULL,
    [UserName] [nvarchar](256) NULL,
    [NormalizedUserName] [nvarchar](256) NULL,
    [Email] [nvarchar](256) NULL,
    [NormalizedEmail] [nvarchar](256) NULL,
    [EmailConfirmed] [bit] NOT NULL,
    [PasswordHash] [nvarchar](max) NULL,
    [SecurityStamp] [nvarchar](max) NULL,
    [ConcurrencyStamp] [nvarchar](max) NULL,
    [PhoneNumber] [nvarchar](max) NULL,
    [PhoneNumberConfirmed] [bit] NOT NULL,
    [TwoFactorEnabled] [bit] NOT NULL,
    [LockoutEnd] [datetimeoffset](7) NULL,
    [LockoutEnabled] [bit] NOT NULL,
    [AccessFailedCount] [int] NOT NULL,
    [Fullname] [nvarchar](max) NULL,
    [AliasName] [nvarchar](max) NULL,
    [SecondMobile] [nvarchar](max) NULL,
    [About] [nvarchar](max) NULL,
    [Created] [datetime2](7) NULL,
    [Modified] [datetime2](7) NULL,

    CONSTRAINT [PK_AspNetUsers] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[trusted_person]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [fullname] [nvarchar](512) NULL,
    [alias_name] [nvarchar](512) NULL,
    [email] [nvarchar](512) NULL,
    [phone_number1] [nvarchar](50) NULL,
    [phone_number2] [nvarchar](50) NULL,
    [phone_number3] [nvarchar](50) NULL,
    [relationship_id] [int] NULL,
    [about] [nvarchar](max) NOT NULL,
    [avatar_id] [int] NULL,
    [created] [datetime] NULL,
    [modified] [datetime] NULL,

    CONSTRAINT [PK_trusted_person] 
        PRIMARY KEY CLUSTERED ([id] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[user_trusted_person]
(
    [user_id] [nvarchar](450) NOT NULL,
    [trusted_person_id] [int] NOT NULL,

    CONSTRAINT [PK_user_trusted_person] 
        PRIMARY KEY CLUSTERED ([user_id] ASC, [trusted_person_id] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO [dbo].[AspNetUsers] ([Id], [UserName], [NormalizedUserName], [Email], [NormalizedEmail], [EmailConfirmed], [PasswordHash], [SecurityStamp], [ConcurrencyStamp], [PhoneNumber], [PhoneNumberConfirmed], [TwoFactorEnabled], [LockoutEnd], [LockoutEnabled], [AccessFailedCount], [Fullname], [AliasName], [SecondMobile], [About], [Created], [Modified]) 
VALUES (N'4485e86d-3c2f-484f-a0b1-29e9c895bf9d', N'hanp2@example.io', N'HANP2@example.io', N'hanp2@example.io', N'HANP2@EXAMPLE.IO', 1, N'AQAAAAEAACcQAAAAELjSfhhpWmlwHb6fwrw9F/6DXQntlZMMcBMLDeiq1Ekkwf7V0IE76Bm7HoHwXUGUNA==', N'GTNEDRSWMSTHAGZVL4N7HOEHECUX3KC5', N'49bc54f6-217a-43f2-872e-b39e0453b90b', N'0909878767', 0, 0, NULL, 1, 0, N'Nguyễn Phương Hà2', N'HaNP2', N'0123456765', N'PGĐ kỹ thuật', CAST(N'2020-09-10T09:36:48.9768760' AS DateTime2), CAST(N'2020-09-10T09:36:48.9771728' AS DateTime2))
GO

INSERT INTO [dbo].[AspNetUsers] ([Id], [UserName], [NormalizedUserName], [Email], [NormalizedEmail], [EmailConfirmed], [PasswordHash], [SecurityStamp], [ConcurrencyStamp], [PhoneNumber], [PhoneNumberConfirmed], [TwoFactorEnabled], [LockoutEnd], [LockoutEnabled], [AccessFailedCount], [Fullname], [AliasName], [SecondMobile], [About], [Created], [Modified]) 
VALUES (N'8ec8b490-bc7d-4468-af7f-9870ccd72981', N'tinlh@example.io', N'TINLH@example.io', N'tinlh@example.io', N'TINLH@EXAMPLE.IO', 0, N'AQAAAAEAACcQAAAAEMN05q50vK31c5v+GBm4JK9YdXXSDnugtplgn+o2u/eU2vJLNOyMl1t7EyENK5BGPQ==', N'3IIN7JKN5XCKPE7POFWFNHLZNHK7ZDKD', N'0559b9ec-9998-4183-84af-224aec4e1d12', NULL, 0, 0, NULL, 1, 0, NULL, NULL, NULL, N'Front-end developer', CAST(N'2020-09-10T08:22:30.1337889' AS DateTime2), CAST(N'2020-09-10T08:22:30.1352528' AS DateTime2))
GO

INSERT INTO [dbo].[AspNetUsers] ([Id], [UserName], [NormalizedUserName], [Email], [NormalizedEmail], [EmailConfirmed], [PasswordHash], [SecurityStamp], [ConcurrencyStamp], [PhoneNumber], [PhoneNumberConfirmed], [TwoFactorEnabled], [LockoutEnd], [LockoutEnabled], [AccessFailedCount], [Fullname], [AliasName], [SecondMobile], [About], [Created], [Modified]) 
VALUES (N'a9e23763-8285-4d4b-b466-e1d5d9287e43', N'hanp@example.io', N'HANP@example.io', N'hanp@example.io', N'HANP@EXAMPLE.IO', 1, N'AQAAAAEAACcQAAAAECIiSI1XQ9kL+8/RIS+ztOU5sU+J2KKFyODpoJPzfD9xL6r3H10WNhi/VITbv2mtfA==', N'QDOW7EB5XF6CIQZ4H4JBRRLSANIIGTHR', N'7eed1e98-ea18-4f4b-b017-c428adc2da73', N'0909878767', 0, 0, NULL, 1, 0, N'Nguyễn Phương Hà', N'HaNP', N'0123456765', N'PGĐ kỹ thuật', CAST(N'2020-09-09T16:38:37.7443193' AS DateTime2), CAST(N'2020-09-09T16:38:37.7444016' AS DateTime2))
GO

SET IDENTITY_INSERT [dbo].[trusted_person] ON 
GO

INSERT INTO [dbo].[trusted_person] ([id], [fullname], [alias_name], [email], [phone_number1], [phone_number2], [phone_number3], [relationship_id], [about], [avatar_id], [created], [modified]) 
VALUES (2, N'Nguyễn Thanh Vân', N'Thanh Vân', N'thanhvan@yahoo.com', N'0989873776', N'0989872777', N'0989876778', 3, N'Nguyen Van', 42, CAST(N'2020-09-10T09:32:07.443' AS DateTime), NULL)
GO

INSERT INTO [dbo].[trusted_person] ([id], [fullname], [alias_name], [email], [phone_number1], [phone_number2], [phone_number3], [relationship_id], [about], [avatar_id], [created], [modified]) 
VALUES (3, N'Nguyen Van An', N'An', N'ductx@example.io', N'0987656545', N'0987656546', N'0987656547', 1, N'abc', 1, CAST(N'2020-09-10T14:38:15.130' AS DateTime), NULL)
GO
SET IDENTITY_INSERT [dbo].[trusted_person] OFF
GO
INSERT [dbo].[user_trusted_person] ([user_id], [trusted_person_id]) VALUES (N'4485e86d-3c2f-484f-a0b1-29e9c895bf9d', 3)
GO

在SQL Server Management Studio中查询成功，返回1条记录。

SELECT * 
FROM trusted_person t
LEFT JOIN user_trusted_person u ON u.trusted_person_id = t.id
LEFT JOIN AspNetUsers a ON a.Id = u.user_id 
WHERE a.Id = '4485e86d-3c2f-484f-a0b1-29e9c895bf9d';

C#代码:

using System;

#nullable disable

namespace shadow.Models
{
    public partial class TrustedPerson
    {
        public int Id { get; set; }
        public string Fullname { get; set; }
        public string AliasName { get; set; }
        public string Email { get; set; }
        public string PhoneNumber1 { get; set; }
        public string PhoneNumber2 { get; set; }
        public string PhoneNumber3 { get; set; }
        public int? RelationshipId { get; set; }
        public string About { get; set; }
        public int? AvatarId { get; set; }
        public DateTime? Created { get; set; }
        public DateTime? Modified { get; set; }
    }
}

DbContext 有 TrustedPeople - TrustedPerson 的复数形式

using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore;
using shadow.Models;

namespace shadow.Data
{
    public partial class ApplicationDbContext : IdentityDbContext<ApplicationUser>
    {
        public ApplicationDbContext()
        {
        }

        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
            : base(options)
        {
        }

        public virtual DbSet<ApplicationUser> ApplicationUsers { get; set; }       
        public virtual DbSet<PaymentRequest> PaymentRequest { get; set; }
        public virtual DbSet<TrustedPerson> TrustedPeople { get; set; }

//SELECT* FROM trusted_person t
//LEFT JOIN user_trusted_person u ON u.trusted_person_id = t.id
//LEFT JOIN AspNetUsers a ON a.Id = u.user_id
//WHERE a.Id = '4485e86d-3c2f-484f-a0b1-29e9c895bf9d';

[HttpGet]
[Route("user")]
public async Task<ActionResult<IEnumerable<TrustedPerson>>> GetAllTrustedPersonsByUserId(string UserId)
{
    var items = _db.TrustedPeople
        .FromSqlRaw("" +
            " SELECT * FROM trusted_person t " +
            " LEFT JOIN user_trusted_person u ON u.trusted_person_id = t.id " +
            " LEFT JOIN AspNetUsers a ON a.Id = u.user_id " +
            " WHERE a.Id = '{0}'", UserId)
        .ToList();
    return Ok(items);
}

错误

System.ArgumentException: An item with the same key has already been added. Key: Id
   at System.Collections.Generic.Dictionary`2.TryInsert(TKey key, TValue value, InsertionBehavior behavior)
   at System.Linq.Enumerable.ToDictionary[TSource,TKey,TElement](IEnumerable`1 source, Func`2 keySelector, Func`2 elementSelector, IEqualityComparer`1 comparer)
   at Microsoft.EntityFrameworkCore.Query.Internal.FromSqlQueryingEnumerable`1.BuildIndexMap(IReadOnlyList`1 columnNames, DbDataReader dataReader)
   at Microsoft.EntityFrameworkCore.Query.Internal.FromSqlQueryingEnumerable`1.Enumerator.InitializeReader(DbContext _, Boolean result)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.FromSqlQueryingEnumerable`1.Enumerator.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at shadow.Controllers.UserTrustedPersonController.GetAllTrustedPersonsByUserId(String UserId) in D:\shadow_backend\Controllers\UserTrustedPersonController.cs:line 77
   at lambda_method9(Closure , Object )
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeFilterPipelineAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authorization.Policy.AuthorizationMiddlewareResultHandler.HandleAsync(RequestDelegate next, HttpContext context, AuthorizationPolicy policy, PolicyAuthorizationResult authorizeResult)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

HEADERS
=======
Cache-Control: no-cache
Connection: keep-alive
Accept: */*
Accept-Encoding: gzip, deflate, br
Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJFbWFpbCI6InRpbmxoQG1wc29sdXRpb25zLmlvIiwiaHR0cDovL3NjaGVtYXMueG1sc29hcC5vcmcvd3MvMjAwNS8wNS9pZGVudGl0eS9jbGFpbXMvbmFtZWlkZW50aWZpZXIiOiIyODI1MTVmZi00ODljLTRmOWMtODQ4Ny1hNzcyZDcyYzMxMDkiLCJleHAiOjE2MDIzMTQ5MzMsImlzcyI6Imh0dHA6Ly9tcHNvbHV0aW9ucy5pbyIsImF1ZCI6Imh0dHA6Ly9tcHNvbHV0aW9ucy5pbyJ9.l0_PH63k05RpjH6lEj7TJWHW0wP8VuhtfWugzs1jjhg
Host: localhost:5002
User-Agent: PostmanRuntime/7.26.5
Postman-Token: 3ecd1393-9865-4e80-93f9-d0f707ee2ad7

如何解决？

Answer 1

出现此错误是因为您查询返回许多名称为“id”的列。

尝试将 select * 替换为 select t.*。

[HttpGet]
[Route("user")]
public async Task<ActionResult<IEnumerable<TrustedPerson>>> GetAllTrustedPersonsByUserId(string UserId)
{
    var items = _db.TrustedPeople
        .FromSqlRaw("" +
            " SELECT t.* FROM trusted_person t " +
            " LEFT JOIN user_trusted_person u ON u.trusted_person_id = t.id " +
            " LEFT JOIN AspNetUsers a ON a.Id = u.user_id " +
            " WHERE a.Id = {0}", UserId)
        .ToList();
    return Ok(items);
}

注意:"WHERE a.Id = {0}", UserId) ，而不是 "WHERE a.Id = '{0}'", UserId)