C#•2y ago

✅ Can you add related data to a DbContext like this

I have these classes where Company.Locations navigates to a Locations table and Location.LocationReviews navigates to a LocationReviews table

public class Company : IEntity
{
        public string Id { get; set; }
        public string Name { get; set; }
        public List<Location> Locations {get;set;}
}
public class Location : IEntity
{
        public string Id { get; set; }
        public Company Company { get; set; }
        public List<LocationReview>  LocationReviews { get; set; } = new();
}
public class LocationReview : IEntity
{
        public string Id { get; set; }
        public Location Location { get; set; }
}

public class Company : IEntity
{
        public string Id { get; set; }
        public string Name { get; set; }
        public List<Location> Locations {get;set;}
}
public class Location : IEntity
{
        public string Id { get; set; }
        public Company Company { get; set; }
        public List<LocationReview>  LocationReviews { get; set; } = new();
}
public class LocationReview : IEntity
{
        public string Id { get; set; }
        public Location Location { get; set; }
}

I have a generic controller that Im now finding out only works for Company entities.

    [HttpPut]
    public async Task<ActionResult> Save([FromBody] TItem entity)
    {
        var result = await _dbContext.Set<TItem>().Where(x => x.Id == entity.Id)
            .AsNoTracking()
            .FirstOrDefaultAsync();
        if (result != null)
        {
            _dbContext.Update(entity);
        }
        else
        {
            await _dbContext.Set<TItem>().AddAsync(entity);
        }
        await _dbContext.SaveChangesAsync();
        return Ok();
    }

    [HttpPut]
    public async Task<ActionResult> Save([FromBody] TItem entity)
    {
        var result = await _dbContext.Set<TItem>().Where(x => x.Id == entity.Id)
            .AsNoTracking()
            .FirstOrDefaultAsync();
        if (result != null)
        {
            _dbContext.Update(entity);
        }
        else
        {
            await _dbContext.Set<TItem>().AddAsync(entity);
        }
        await _dbContext.SaveChangesAsync();
        return Ok();
    }

26 Replies

ConnorOP•2y ago

If I put in a Location or LocationReview, I get an error like this.

Violation of PRIMARY KEY constraint 'PK_Companies'. Cannot insert duplicate key in object 'dbo.Companies'. The duplicate key value is (0813fe13-9646-45a5-ab2c-e3a2cd46d54a).
      Violation of PRIMARY KEY constraint 'PK_Locations'. Cannot insert duplicate key in object 'dbo.Locations'. The duplicate key value is (1360a25e-1713-46da-98ea-5ca92e3799db).

Violation of PRIMARY KEY constraint 'PK_Companies'. Cannot insert duplicate key in object 'dbo.Companies'. The duplicate key value is (0813fe13-9646-45a5-ab2c-e3a2cd46d54a).
      Violation of PRIMARY KEY constraint 'PK_Locations'. Cannot insert duplicate key in object 'dbo.Locations'. The duplicate key value is (1360a25e-1713-46da-98ea-5ca92e3799db).

The way I feel like it's supposed to work is when I put in a LocatonReview, it will check the Location and Company it belongs to and then add it. The problem is, it is acting as if I'm trying to add a new Company and Location. Am I doing something wrong, is there a work around? Here is the JSON of what I'm trying to add if it helps

{
    "$id": "1",
    "Id": "a6814f7f-0939-4f42-8b48-161c051cd141",
    "Location": {
        "$id": "2",
        "Id": "1360a25e-1713-46da-98ea-5ca92e3799db",
        "Company": {
            "$id": "3",
            "Id": "0813fe13-9646-45a5-ab2c-e3a2cd46d54a",
            "Name": "Dooley LLC",
            "Locations": {
                "$id": "4",
                "$values": [
                    {
                        "$ref": "2"
                    }
                ]
            }
        }
    }
}

{
    "$id": "1",
    "Id": "a6814f7f-0939-4f42-8b48-161c051cd141",
    "Location": {
        "$id": "2",
        "Id": "1360a25e-1713-46da-98ea-5ca92e3799db",
        "Company": {
            "$id": "3",
            "Id": "0813fe13-9646-45a5-ab2c-e3a2cd46d54a",
            "Name": "Dooley LLC",
            "Locations": {
                "$id": "4",
                "$values": [
                    {
                        "$ref": "2"
                    }
                ]
            }
        }
    }
}

Saber•2y ago

of course something like this isn't going to work when you use ef incorrectly

ConnorOP•2y ago

Can you provide guidance then please? Where my mistakes are and how to fix them?

Saber•2y ago

A generic endpoint like this simply isn't going to work for what you want

ConnorOP•2y ago

I mean, every method except save already works What exactly doesn’t work

Saber•2y ago

"except save" oh so it doesn't work

ConnorOP•2y ago

I wasn’t disagreeing, I’m just asking what is about save that I’m doing incorrectly

Saber•2y ago

Just calling Update on EF is almost never advised. As you can see here, its going to attempt to insert new entities instead of updating something you think should be updated. EF isn't going to check if all your child entities exist, it figures because they are not tracked, you are adding them

ConnorOP•2y ago

Thanks for the advice. What is the better way to do it?

Saber•2y ago

Nothing generic if your expecting to pass over all that json.

ConnorOP•2y ago

I can get rid of the generics. They just let me only have to write one data access class

Saber•2y ago

well you'd want to load in the relevant data, update anything that existed if this is going to accept updating/adding multiple locations/reviews, and insert anything new

ConnorOP•2y ago

Are you saying I should do this? Or something different?

C#
        var company = await _dbContext.Companies.Where(c => c.Id == locationReview.Location.Company.Id)
            .Include(c => c.Locations)
            .ThenInclude(l => l.LocationReviews)
            .FirstOrDefaultAsync();
        var location = company.Locations.Where(l => l.Id == locationReview.Location.Id).FirstOrDefault();
        location.LocationReviews.Add();
        await _dbContext.SaveChangesAsync();

C#
        var company = await _dbContext.Companies.Where(c => c.Id == locationReview.Location.Company.Id)
            .Include(c => c.Locations)
            .ThenInclude(l => l.LocationReviews)
            .FirstOrDefaultAsync();
        var location = company.Locations.Where(l => l.Id == locationReview.Location.Id).FirstOrDefault();
        location.LocationReviews.Add();
        await _dbContext.SaveChangesAsync();

I feel like this should be able to be simplified

Saber•2y ago

Are you only trying to add a single location or review at a time here

ConnorOP•2y ago

Yes

Saber•2y ago

Then I guess you could simplify this by adding the LocationId or CompanyId properties onto your entities like they should already have, and pass that over instead of the respective object

ConnorOP•2y ago

Good idea.

       
        var location = _dbContext.Locations.Where(l => l.Id == locationId).FirstOrDefault();
        location.LocationReviews.Add();
        await _dbContext.SaveChangesAsync();

       
        var location = _dbContext.Locations.Where(l => l.Id == locationId).FirstOrDefault();
        location.LocationReviews.Add();
        await _dbContext.SaveChangesAsync();

Would this be sufficient without have to access the company

Saber•2y ago

you'd probably even be able to just use the generic method if you really wanted to.

public class LocationReview {
  public string Id {get;set;}
  public string LocationId {get;set;}
}

public class LocationReview {
  public string Id {get;set;}
  public string LocationId {get;set;}
}

if you attempt to add a review, its going to bitch if the locationid doesn't actually exist assuming you have a fk relation setup

ConnorOP•2y ago

How would the database be set up in this example. Would I have to tell the modelBuilder that LocationId is a foreign key. Would there be no relationship at all and I just query the Location it belongs to if I need it?

Saber•2y ago

your database should already have that field, you just didn't have it on your model. You had the Location object on it, which EF will know about that relationship from the LocationId that it uses in the database. Having LocationId and Location properties on your model ef will know about the relationship by convention

ConnorOP•2y ago

Then the only real difference would be that the model now has a reference to the id instead of the Location itself?

Saber•2y ago

you'd put both properties on the model so the relationship is there to be used in code

ConnorOP•2y ago

public class LocationReview {
  public string Id {get;set;}
  public Location Location {get;set;}
  public string LocationId {get;set;}
}

public class LocationReview {
  public string Id {get;set;}
  public Location Location {get;set;}
  public string LocationId {get;set;}
}

Saber•2y ago

yeah

ConnorOP•2y ago

Okay appreciate all the help

Accord•2y ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

Gaming

Programming

✅ Can you add related data to a DbContext like this