C#•14mo ago

❔ EFCore: Unique Category Name

I have an issue with the entity "Category" that I have. Each "Target" Entity can have several categories, each category has a name and an Id. Target Entity has a List of Categories, however now when trying to add categories to an Entity there are duplicates. If I have two entities and add a category with the same name to both, there will be a dupe category (as the name exists two times and the name of Category entity should be unique)

19 Replies

KroksOP•14mo ago

Target
+----+-------------+
| Id | OtherProps  |
+----+-------------+
| 1  | ...         |
| 2  | ...         |
+----+-------------+

Category
+----+--------+
| Id | Name   |
+----+--------+
| 1  | abc    |
| 2  | xyz    |
+----+--------+

TargetCategory (Join Table)
+------------------+-------------------+
| TargetId   | CategoryId |
+------------------+-------------------+
| 1                | 1                 |
| 1                | 2                 |
| 2                | 2                 |
+------------------+-------------------+

Target
+----+-------------+
| Id | OtherProps  |
+----+-------------+
| 1  | ...         |
| 2  | ...         |
+----+-------------+

Category
+----+--------+
| Id | Name   |
+----+--------+
| 1  | abc    |
| 2  | xyz    |
+----+--------+

TargetCategory (Join Table)
+------------------+-------------------+
| TargetId   | CategoryId |
+------------------+-------------------+
| 1                | 1                 |
| 1                | 2                 |
| 2                | 2                 |
+------------------+-------------------+

so I aim for this structure, what is the easiest way to achieve it with efcore?

    public class Target
    {
        public int Id { get; set; }
        public List<Category>? Categories { get; set; }
    }
    public class Category
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    public class Target
    {
        public int Id { get; set; }
        public List<Category>? Categories { get; set; }
    }
    public class Category
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

the two models

Pobiega•14mo ago

Just configure a unique index on the Category.Name property, either with (bad)annotations or (good) IEntityConfiguration

KroksOP•14mo ago

oh damn that rlly all? haha

Pobiega•14mo ago

well, yes?

KroksOP•14mo ago

didnt think of it but yeah makes sense

Pobiega•14mo ago

it wont automagically work when inserting a new Target thou, just fyi

KroksOP•14mo ago

so now if I add category to Target A and B with same name it does map it via the ID of the category and not create dupe?

Pobiega•14mo ago

yes, assuming tracked entities

KroksOP•14mo ago

ok will check

Pobiega•14mo ago

var newTarget = new Target()
{
    Categories = new()
    {
        new Category()
        {
            Name = "A" // lets assume A already exists
        }
    }
};

_context.Targets.Add(newTarget);
await _context.SaveChangesAsync();

var newTarget = new Target()
{
    Categories = new()
    {
        new Category()
        {
            Name = "A" // lets assume A already exists
        }
    }
};

_context.Targets.Add(newTarget);
await _context.SaveChangesAsync();

this will fail because A already exists it wont look up the category and map it, you'd have to do that yourself, but that would be the same if not using EF

KroksOP•14mo ago

it doesnt rlly fail it just updates the referenced ID. The structure is not correct in the DB, not as I wanted it to be. How can I tell it that one category is not bound to one ScrapeTarget? but that multiple targets can have the same category so it uses my mapping that I wished

KroksOP•14mo ago

this is category table

Pobiega•14mo ago

Ah right, since you dont have a reverse nav property it doesnt assume many to many so either add the reverse nav property, or manually configure the relationship in your IEntityConfiguration

KroksOP•14mo ago

ok I fixed that. Now there is the issue that I cannot add two targets with same category due to unique constraint. I assume because it is trying to add the category again but it cannot. Not sure whats best solution for this

using (var ctx = new BotContext())
{
    await ctx.Database.EnsureCreatedAsync();

    var category = new ScrapeCategory
    {
        Name = "test"
    };

    var target = new ScrapeTarget
    {
        ScreenName = "test",
        RestID = "test",
        Categories = new List<ScrapeCategory> { category },
    };


    var target2 = new ScrapeTarget
    {
        ScreenName = "test",
        RestID = "test",
        Categories = new List<ScrapeCategory> { category },
    };


    //add

    await ctx.ScrapeTargets.AddAsync(target);
    await ctx.ScrapeTargets.AddAsync(target2);

    await ctx.SaveChangesAsync();
}

using (var ctx = new BotContext())
{
    await ctx.Database.EnsureCreatedAsync();

    var category = new ScrapeCategory
    {
        Name = "test"
    };

    var target = new ScrapeTarget
    {
        ScreenName = "test",
        RestID = "test",
        Categories = new List<ScrapeCategory> { category },
    };


    var target2 = new ScrapeTarget
    {
        ScreenName = "test",
        RestID = "test",
        Categories = new List<ScrapeCategory> { category },
    };


    //add

    await ctx.ScrapeTargets.AddAsync(target);
    await ctx.ScrapeTargets.AddAsync(target2);

    await ctx.SaveChangesAsync();
}

this would throw

Pobiega•14mo ago

no, that should work fine you also dont need to use await AddAync just use Add you are not using HiLo I assume

KroksOP•14mo ago

you are right, my bad. it works like this is there any limitation i need to consider? I guess two instances with same name would not work for category then it would throw probably but thats ok

Pobiega•14mo ago

correct

KroksOP•14mo ago

Alright thanks for the help, new to efcore so kinda still nooby but once learned its a lot more efficient to code, no need for nasty sql queries anymore

Accord•14mo 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

❔ EFCore: Unique Category Name