C#•3y 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; }
}

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();
    }

    [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();
    }

ConnorOP•1/5/23, 2:11 AM

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?

ConnorOP•1/5/23, 2:13 AM

Here is the JSON of what I'm trying to add if it helps

ConnorOP•1/5/23, 2:14 AM

{
    "$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•1/5/23, 3:43 AM

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

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

ConnorOP•1/5/23, 3:53 AM

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

Saber•1/5/23, 3:54 AM

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

ConnorOP•1/5/23, 3:54 AM

I mean, every method except save already works

ConnorOP•1/5/23, 3:54 AM

What exactly doesn’t work

Saber•1/5/23, 3:54 AM

"except save" oh so it doesn't work

ConnorOP•1/5/23, 3:55 AM

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

Saber•1/5/23, 3:57 AM

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•1/5/23, 3:58 AM

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

Saber•1/5/23, 4:00 AM

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

ConnorOP•1/5/23, 4:01 AM

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

Saber•1/5/23, 4:06 AM

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

SSaber well you'd want to load in the relevant data, update anything that existed if th...

ConnorOP•1/5/23, 4:11 AM

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();

ConnorOP•1/5/23, 4:12 AM

I feel like this should be able to be simplified

Saber•1/5/23, 4:12 AM

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

ConnorOP•1/5/23, 4:12 AM

Yes

Saber•1/5/23, 4:13 AM

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•1/5/23, 4:14 AM

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•1/5/23, 4:16 AM

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

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

ConnorOP•1/5/23, 4:21 AM

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•1/5/23, 4:22 AM

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

SSaber your database should already have that field, you just didn't have it on your mo...

ConnorOP•1/5/23, 4:24 AM

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

Saber•1/5/23, 4:25 AM

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

SSaber you'd put both properties on the model so the relationship is there to be used i...

ConnorOP•1/5/23, 4:25 AM

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•1/5/23, 4:25 AM

yeah

ConnorOP•1/5/23, 4:27 AM

Okay appreciate all the help

Accord•1/6/23, 4:28 AM

Was this issue resolved? If so, run

/close

/close

otherwise I will mark this as stale and this post will be archived until there is new activity.

✅ Can you add related data to a DbContext like this

Similar Threads

Similar Threads

Similar Threads