Model's indexes doesn't validate

Question

Table in postgres:

CREATE TABLE "public"."filters" (
  "Id" int4 NOT NULL DEFAULT nextval('"filters_Id_seq"'::regclass),
  "Name" varchar(200) COLLATE "pg_catalog"."default" NOT NULL DEFAULT ''::character varying,
  "Type" int4 NOT NULL,
  "Alias" varchar(200) COLLATE "pg_catalog"."default" NOT NULL DEFAULT ''::character varying
)
;

-- ----------------------------
-- Indexes structure for table filters
-- ----------------------------
CREATE UNIQUE INDEX "filters_unique_alias_key" ON "public"."filters" USING btree (
  "Alias" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);
CREATE UNIQUE INDEX "filters_unique_name_key" ON "public"."filters" USING btree (
  "Name" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

-- ----------------------------
-- Primary Key structure for table filters
-- ----------------------------
ALTER TABLE "public"."filters" ADD CONSTRAINT "filters_primary_key" PRIMARY KEY ("Id");

Model:

[Table("filters")]
    public class Filter
    {
        [Key] public int Id { get; set; }

        [Required]
        [StringLength(200)]
        public string Name { get; set; }

        [Required]
        [StringLength(200)]
        public string Alias { get; set; }

        [Required] public FilterType Type { get; set; }
    }

    public enum FilterType
    {
        Logic = 0,
        Text = 1,
        Number = 2
    }

Context:

protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);

            builder.Entity<Filter>().HasIndex(model => model.Name).HasName("filters_unique_name_key").IsUnique();
            builder.Entity<Filter>().HasIndex(model => model.Alias).HasName("filters_unique_alias_key").IsUnique();
        }

I'm making post request with NOT unique values and receive DbUpdateException from postgres:

public IActionResult Create(Filter filter = null)
        {
            if (ModelState.IsValid && filter != null)
            {

                // code

            }
        }

Why if I defined indexes by the fluent api a modelstate is valid but index validation isn't working? Seems ef core ignore indexes validation but why? How to validate it?

Chris Pratt Chris Pratt · Accepted Answer · 2019-04-23T19:29:42

The unique index fluent API config doesn't affect model validation. It can't know whether a particular posted value is unique or not without querying the database, which is not something the modelbinder is empowered to do or even should do. If you want to add validation for this, you'll need to do so manually.

var filter = await _context.Filters.FirstOrDefaultAsync(x => x.Name == model.Name || x.Alias == model.Alias);
if (filter?.Name == model.Name)
    ModelState.AddModelError(nameof(model.Name), "Name must be unique.");
if (filter?.Alias == model.Alias)
    ModelState.AddModelError(nameof(model.Alias), "Alias must be unique.");

Model's indexes doesn't validate

1 Answers