CODEWITHSUNDEEP
c#sql-server.net-coreasp.net-core-mvcasp.net-core-webapi
drethedevjs
drethedevjs

Reputation: 484

GET Call: An item with the same key has already been added. Key: Id

I am trying to use EF Core to call a stored procedure in a SQL Server db. The db server is hosted using Docker. The problem is when I make a GET call to the controller to hit the service, it stalls for a little while and then I get this error...

ArgumentException: An item with the same key has already been added. Key: Id

Here's my code:

Models

public interface IGiftList
{
   int Id { get; set; }
   string Occasion { get; set; }
   int UserId { get; set; }
   string Alias { get; set; }
   bool AnotherRecipient { get; set; }
   string RecipientName { get; set; }
   int RecipientAddressId { get; set; }
}

public class GiftList : IGiftList
{
   public int Id { get; set; }
   public string Occasion { get; set; }
   public int UserId { get; set; }
   public string Alias { get; set; }
   public bool AnotherRecipient { get; set; }
   public string RecipientName { get; set; }
   public int RecipientAddressId { get; set; }
   public List<Gift> Items { get; set;}
}

Controller

[HttpGet, Route("lists")]
public List<GiftList> GetUserLists([FromQuery] int userId)
{
   var lists = _giftService.GetUserLists(userId).Select(l => (GiftList)l).ToList();
   return lists;
}

Service

public List<IGiftList> GetUserLists(int userId)
{
   var items = this.giftContext.Lists.FromSqlRaw($"EXECUTE dbo.GetUserLists @UserId={userId}").ToList<IGiftList>(); <--Exception gets raised here.
   return items;
}

Stored Procedure

CREATE PROCEDURE dbo.GetUserLists 
(
    @UserId INT
)
AS
DECLARE @ListId INT

SET @ListId = (SELECT TOP (1) Id FROM Lists WHERE UserId = @UserId);

SELECT 
    L.Id,
    O.Title,
    U.Id,
    L.Alias,
    L.AnotherRecipient,
    L.RecipientName,
    L.RecipientAddressId
FROM Lists AS L
JOIN Occasions AS O
    ON O.Id = L.OccasionId
JOIN Users AS U
    ON U.Id = L.UserId
WHERE
    L.UserId = @UserId

Any idea why I might be getting this error?

Upvotes: 1

Views: 1743

Answers (1)

Md Farid Uddin Kiron
Md Farid Uddin Kiron

Reputation: 22409

Its because of your Store Procedure's two fields which are L.Id and U.Id those are treating as Id but when it comes to bind with your model property which are Id and UserId the compliler gets problem to mapping with two. So the best way is to handle this using SQL Alias AS.

U.Id AS UserId would resolve your problem.

Note: If the primary keys in the select query's are same remember to make them seperate by uisng AS other than it will mappped as same key.

Upvotes: 2

Related Questions