BUMP EF Core 5.0 model customization needed - Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'BIdNavigationBId'.

Question

User379720387 posted

I have a Client table with 5 foreign keys: OwnerLocation, OwnerBillTo, EnteredByProvider,  sId, gId, bId

SqlServer finds a problem as per the title for bId, and I assume will do the same for sId and gId, once bId issue is taken care off.

All these relatioships are 1 Client has 1 of type foreign key

The EF Core was scaffolded from a db.

Client:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

#nullable disable

namespace BtApiCore.Model
{
    
    public partial class Client
    {
        public Client()
        {
            Appointments = new HashSet<Appointment>();
            ClientOwners = new HashSet<ClientOwner>();
            ClientRecords = new HashSet<ClientRecord>();
            ClientShoes = new HashSet<ClientShoe>();
            CpPictures = new HashSet<CpPicture>();
            TagTiles = new HashSet<TagTile>();
            ToDoClientRs = new HashSet<ToDoClientR>();
            TodoRecords = new HashSet<TodoRecord>();
            Transactions = new HashSet<Transaction>();
        }

        [Key] public int ClientId { get; set; }
        public string Cname { get; set; }
        public string Cdescription { get; set; }        
        public int? OwnerLocationId { get; set; }
        public bool? BillEmail { get; set; }
        public string ImageName { get; set; }
        public int? PropertyId { get; set; }
        public bool? BillMonthly { get; set; }
        public int? EnteredByProviderId { get; set; }
        public bool? IsLinked { get; set; }
        public int? OwnerBillToId { get; set; }
        public bool? BillLocation { get; set; }
        public int? SId { get; set; }
        public int? GId { get; set; }
        public DateTime? DofB { get; set; }
        public int? BId { get; set; }
        public string Rfid { get; set; }
        public byte[] ImgProfile { get; set; }

        [ForeignKey("BId")]
        public virtual Breed BIdNavigation { get; set; }
        
        public virtual Provider EnteredByProvider { get; set; }
        
        [ForeignKey("GId")] 
        public virtual EqGender GIdNavigation { get; set; }
        
        [ForeignKey("OwnerBillToId")]
        public virtual Owner OwnerBillTo { get; set; }
        
        [ForeignKey("OwnerLocationId")]
        public virtual Owner OwnerLocation { get; set; }
        
        [ForeignKey("SId")]
        public virtual EqSize SIdNavigation { get; set; }
        public virtual ICollection<Appointment> Appointments { get; set; }
        public virtual ICollection<ClientOwner> ClientOwners { get; set; }
        public virtual ICollection<ClientRecord> ClientRecords { get; set; }
        public virtual ICollection<ClientShoe> ClientShoes { get; set; }
        public virtual ICollection<CpPicture> CpPictures { get; set; }
        public virtual ICollection<TagTile> TagTiles { get; set; }
        public virtual ICollection<ToDoClientR> ToDoClientRs { get; set; }
        public virtual ICollection<TodoRecord> TodoRecords { get; set; }
        public virtual ICollection<Transaction> Transactions { get; set; }
    }
}

And the modelbuilder context:

modelBuilder.Entity<Client>(entity =>
            {
                entity.HasKey(e => e.ClientId)
                    .HasName("PK_Clients_ClientId");

                entity.HasIndex(e => e.OwnerLocationId, "fk_stabklesInBarn");

                entity.Property(e => e.BId).HasColumnName("bId");

                entity.Property(e => e.BillLocation).HasColumnName("billLocation");

                entity.Property(e => e.Cdescription)
                    .HasMaxLength(50)
                    .HasColumnName("CDescription");

                entity.Property(e => e.Cname)
                    .HasMaxLength(50)
                    .HasColumnName("CName");

                entity.Property(e => e.DofB).HasColumnType("date");

                entity.Property(e => e.GId).HasColumnName("gId");

                entity.Property(e => e.ImageName).HasMaxLength(50);

                entity.Property(e => e.ImgProfile).HasColumnName("imgProfile");

                entity.Property(e => e.OwnerBillToId).HasColumnName("ownerBillToId");

                entity.Property(e => e.OwnerLocationId).HasColumnName("ownerLocationId");

                entity.Property(e => e.Rfid)
                    .HasMaxLength(30)
                    .HasColumnName("RFId");

                entity.Property(e => e.SId).HasColumnName("sId");

                entity.HasOne(d => d.BIdNavigation)
                    .WithMany(p => p.Clients)
                    .HasForeignKey(d => d.BId)
                    .HasConstraintName("FK_Clients_Breed");

                entity.HasOne(d => d.EnteredByProvider)
                    .WithMany(p => p.Clients)
                    .HasForeignKey(d => d.EnteredByProviderId)
                    .HasConstraintName("FK_EnteredByProvider1");

                entity.HasOne(d => d.GIdNavigation)
                    .WithMany(p => p.Clients)
                    .HasForeignKey(d => d.GId)
                    .HasConstraintName("FK_Clients_eqGender");

                entity.HasOne(d => d.OwnerBillTo)
                    .WithMany(p => p.ClientOwnerBillTos)
                    .HasForeignKey(d => d.OwnerBillToId)
                    .HasConstraintName("fk_billedToOwner");

                entity.HasOne(d => d.OwnerLocation)
                    .WithMany(p => p.ClientOwnerLocations)
                    .HasForeignKey(d => d.OwnerLocationId)
                    .HasConstraintName("FK_locatedAtOwner");

                entity.HasOne(d => d.SIdNavigation)
                    .WithMany(p => p.Clients)
                    .HasForeignKey(d => d.SId)
                    .HasConstraintName("FK_Clients_eqSize");
            });

The foreign keys bId, sIg, and gId all have the word "Navigation". The related table modelbuilder is  below

modelBuilder.Entity<Breed>(entity =>
            {
                entity.HasKey(e => e.BId);

                entity.ToTable("Breed");

                entity.Property(e => e.BId).HasColumnName("bId");

                entity.Property(e => e.Breed1)
                    .IsRequired()
                    .HasMaxLength(50)
                    .HasColumnName("Breed");

            });

Note that foreign key EnteredByProvider does not have the word "Navigation" Added.

Not understanding what the significance is when "Navigation" (yes, I did searchgets added and how this model needs to be customized so there is no exception.

Answer 1

User475983607 posted

I think the error has to do with a schema design bug mixed with not following EF naming conventions where column names end with "ID".  It seems the intent is the "Client" is on the many side of several one-to-many relationships.   The way the code is written, there is a table named BidNavigation.  That has a unique BidNavigationBid column points to the Client table.  The Client table can have many records with the same ID.  The schema design does not seem correct and the fluent syntax is not correct.

I'm not sure what you are trying to do.  See the relationship reference docs.

https://docs.microsoft.com/en-us/ef/core/modeling/relationships?tabs=fluent-api%2Cfluent-api-simple-key%2Csimple-key

Answer 2

User379720387 posted

bId, gId, sId are the names of the primary keys in 3 lookup tables, specifying a type characteristic of Client.

In the diagram in SSMS there is a many symbol on the Client side and a key symbol on the Type side

In words, this is a one to many relationship::

There are many Clients who have one Breed Type

One Client has one Breed Type

After reviewing the link provided by mgebhard, and watching some more youtube videos I thin what I have should work, but I am still no further than I was before Christmas.

Gut feeling says it has something to do with the addition of "Navigation" to the name of the primary key

Where do I go from here? Stack Overflow is a non-starter.

Answer 3

User475983607 posted

Try removing the foreign key attribute from the Breed property.  The one to many pattern is covered in the linked docs and has the following pattern.  The Bid property is a foreign key field.

public partial class Client
{
    public int Bid {get; get;}
    public Breed Breed { get; set; }
}

Answer 4

User379720387 posted

Thnx for your input.

There are no squiggles on the Entity class, however Context.cs blows up:

modelBuilder.Entity<Client>(entity =>
            {
                entity.HasKey(e => e.ClientId)
                    .HasName("PK_Clients_ClientId");

                entity.Property(e => e.BillLocation).HasColumnName("billLocation");

                entity.Property(e => e.Cdescription)
                    .HasMaxLength(50)
                    .HasColumnName("CDescription");

                entity.Property(e => e.Cname)
                    .HasMaxLength(50)
                    .HasColumnName("CName");

                entity.Property(e => e.BId).HasColumnName("bId");

                entity.Property(e => e.GId).HasColumnName("gId");

                entity.Property(e => e.SId).HasColumnName("sId");

                entity.Property(e => e.EnteredByProviderId).HasColumnName("enteredByProviderId");


                entity.Property(e => e.OwnerBillToId).HasColumnName("ownerBillToId");

                entity.Property(e => e.OwnerLocationId).HasColumnName("ownerLocationId");

                
                entity.HasOne(d => d.BIdNavigation)
                    .WithMany(p => p.Clients)
                    .HasForeignKey(d => d.BId)
                    .HasConstraintName("FK_Clients_Breed");


}

The first squiggle (pink) is on e.BId: Client does not contain a definition for BId or.... 

The other squiggle is on the HasOne (highlighted).

The type 'int' must be a reference type in order to use it as parameter 'TRelatedEntity' in the generic type or method 'EntityTypeBuilder<Client>.HasOne<TRelatedEntity>(Expression<Func<Client, TRelatedEntity>>)' BtApiCore 

I found this but no idea how to apply that the my scenario.

Answer 5

User-821857111 posted

The first squiggle (pink) is on e.BId: Client does not contain a definition for BId

If you copied mgebhard's code directly, the property was renamed to Bid, not BId.

Answer 6

User379720387 posted

I did not realize it was an attempt to rename something, ok. With that change now implement here is where I am:

public partial class Breed
    {
        public Breed()
        {
            Clients = new HashSet<Client>();
        }
        [Key]
        public int BId { get; set; }
        public string Breed1 { get; set; }

        public virtual ICollection<Client> Clients { get; set; }
    }

modelBuilder.Entity<Breed>(entity =>
            {
                entity.HasKey(e => e.BId);

                entity.ToTable("Breed");

                entity.Property(e => e.BId).HasColumnName("bId");

                entity.Property(e => e.Breed1)
                    .IsRequired()
                    .HasMaxLength(50)
                    .HasColumnName("Breed");
            });

public partial class Client
    {
        public Client()
        {
            Transactions = new HashSet<Transaction>();
        }

        [Key] public int ClientId { get; set; }
        public string Cname { get; set; }
        public string Cdescription { get; set; }        
        public int? OwnerLocationId { get; set; }
        public int? OwnerBillToId { get; set; }
        public bool? BillLocation { get; set; }
        
        //[ForeignKey("BId")]
        public int Bid { get; set; }
        public virtual Breed Breed { get; set; }

        [ForeignKey("EnteredByProviderId")]
        public int? EnteredByProviderId { get; set; }
        public virtual Provider EnteredByProvider { get; set; }

        [ForeignKey("OwnerBillToId")]
        public virtual Owner OwnerBillTo { get; set; }
        
        [ForeignKey("OwnerLocationId")]
        public virtual Owner OwnerLocation { get; set; }
        
       
        public virtual ICollection<Transaction> Transactions { get; set; }
    }

modelBuilder.Entity<Client>(entity =>
            {
                entity.HasKey(e => e.ClientId)
                    .HasName("PK_Clients_ClientId");

                entity.HasIndex(e => e.OwnerLocationId, "fk_stabklesInBarn");

                entity.Property(e => e.BillLocation).HasColumnName("billLocation");

                entity.Property(e => e.Cdescription)
                    .HasMaxLength(50)
                    .HasColumnName("CDescription");

                entity.Property(e => e.Cname)
                    .HasMaxLength(50)
                    .HasColumnName("CName");

                entity.Property(e => e.Bid).HasColumnName("bId");

                entity.Property(e => e.EnteredByProviderId).HasColumnName("enteredByProviderId");


                entity.Property(e => e.OwnerBillToId).HasColumnName("ownerBillToId");

                entity.Property(e => e.OwnerLocationId).HasColumnName("ownerLocationId");


                entity.HasOne(d => d.?)
                    .WithMany(p => p.Clients)
                    .HasForeignKey(d => d.BId)
                    .HasConstraintName("FK_Clients_Breed");

                entity.HasOne(d => d.OwnerBillTo)
                    .WithMany(p => p.ClientOwnerBillTos)
                    .HasForeignKey(d => d.OwnerBillToId)
                    .HasConstraintName("FK_billedToOwner");

                entity.HasOne(d => d.OwnerLocation)
                    .WithMany(p => p.ClientOwnerLocations)
                    .HasForeignKey(d => d.OwnerLocationId)
                    .HasConstraintName("FK_locatedAtOwner");

            });

At the highlighted ? intellisense suggests Bid, but that trigger a squiggle on HasOne() on the same line with:

The type 'int' must be a reference type in order to use it as parameter 'TRelatedEntity' in the generic type or method 'EntityTypeBuilder<Client>.HasOne<TRelatedEntity>(Expression<Func<Client, TRelatedEntity>>)'

Answer 7

User-821857111 posted

entity.HasOne(d => d.Breed)
                    .WithMany(p => p.Clients)
                    .HasForeignKey(d => d.BId)
                    .HasConstraintName("FK_Clients_Breed");

Answer 8

User379720387 posted

There was a squiggle on d.BId, so I changed that to d.Bid. That allowed me to build.

That resulted in:

Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'BreedBId'.
Invalid column name 'GIdNavigationGId'.
Invalid column name 'SIdNavigationSId'.
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__169_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.Tasks.Task.<>c.<.cctor>b__277_0(Object obj)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at BtApiCore.Controllers.ClientController.Get() in C:\Users\Robert\source\Repos\BtApiCore\Controllers\ClientController.cs:line 24
   at lambda_method5(Closure , Object )
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
   at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)
ClientConnectionId:a714b6d3-d9e6-4d53-b458-b3d6877a8e5f
Error Number:207,State:1,Class:16

The only place where BId exists is BTContext:

modelBuilder.Entity<Breed>(entity =>
            {
                entity.HasKey(e => e.BId);

                entity.ToTable("Breed");

                entity.Property(e => e.BId).HasColumnName("bId");

                entity.Property(e => e.Breed1)
                    .IsRequired()
                    .HasMaxLength(50)
                    .HasColumnName("Breed");
            });

Autocomplete has just two options after "e." those are BId and Breed1

For good form I changed those to Breed1, since we already tried BId. Builds OK, but results in same error message.

Answer 9

User379720387 posted

With some expert help looking at things it became clear that I was using the wrong AppDbContext.

Using the other one everything fell into place.