EF create table relation does not exist C#-CodePudding

I'm working with ASP.NET MVC C# and I found the following problem and I really don't know if I'm doing it right or not, it turns out that I create a database with Code First in an API project, but now I'm using the same database of data in a web project creating the model with Entity Framework, I don't want to mix the two projects. In the entire database there is a table related from many to many called CategoriaNegocios, which is the relationship between Categoria and Negocio, so far so good, but when saving the categorias from negocio I get an error that no finds the NegocioCategorias table, so my query is Why do I change the name of the table?

I will leave code used in the API Class Negocio

public class Negocio
    {
        public Guid NegocioId { get; set; }
        [Required]
        public string RazonSocial { get; set; }
        [Required]
        public string NombreComercial { get; set; }
        public virtual ICollection<Categoria> Categoria{ get; set; }
    }

Class Categoria

public class Categoria
    {
        [Key]
        public Guid CategoriaId { get; set; }
        [Required]
        public string Name{ get; set; }
        public string Description { get; set; }
        public virtual ICollection<Negocio> Negocio{ get; set; }
    }

Context

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
    {
        public ApplicationDbContext()
            : base("DefaultConnection", throwIfV1Schema: false)
        {
        }
        
        public static ApplicationDbContext Create()
        {
            return new ApplicationDbContext();
        }

        public DbSet<Negocio> Negocio{ get; set; }
        public DbSet<Categoria> Categoria{ get; set; }
    }

DataBase enter image description here

Code in Web using EF from database already created Class Business

public partial class Negocios
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public Negocios()
        {
            this.Categorias = new HashSet<Categorias>();
        }
        [Key]
        public System.Guid NegocioId { get; set; }
        public string RazonSocial { get; set; }
        public string NombreComercial { get; set; }
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<Categorias> Categorias{ get; set; }
    }

Class Categorias

public partial class Categorias
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public Categorias()
        {
            this.Negocios = new HashSet<Negocios>();
        }
        [Key]
        public System.Guid CategoriaId { get; set; }
        public string Nombre { get; set; }
        public string Descripcion { get; set; }
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<Negocios> Negocios { get; set; }
    }

Code to add a new negocio

var cates = Newtonsoft.Json.JsonConvert.DeserializeObject<List<string>>(negocios.Categorias);
var categorias = await db.Categorias.Where(c => cates.Contains(c.CategoriaId.ToString())).ToListAsync();
negocio.Categorias = categorias;
direccion.Historial = Utils.Utilidad.Historial(direccion, "");
negocio.Direccions = direccion;
negocio.Historial = Utils.Utilidad.Historial(negocios, "");
db.Negocios.Add(negocio);
await db.SaveChangesAsync();

In the SaveChangesAsync() it gives the error

Error image enter image description here

Error Stack

[SqlException (0x80131904): El nombre de objeto 'dbo.NegociosCategorias' no es válido.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) 3331296 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) 334 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) 4289 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) 514 System.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader(Boolean isInternal, Boolean forDescribeParameterEncryption) 237 System.Data.SqlClient.SqlCommand.InternalEndExecuteNonQuery(IAsyncResult asyncResult, String endMethod, Boolean isInternal) 890 System.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult) 288 System.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult) 177 System.Threading.Tasks.TaskFactory1.FromAsyncCoreLogic(IAsyncResult iar, Func2 endFunction, Action1 endAction, Task`1 promise, Boolean requiresSynchronization) 59 System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() 32 System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) 62 System.Data.Entity.Core.Mapping.Update.Internal.d__8.MoveNext() 2574 System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() 32 System.Data.Entity.Core.Mapping.Update.Internal.d__39.MoveNext() 595

[UpdateException: An error occurred while updating the entries. See the inner exception for details.] System.Data.Entity.Core.Mapping.Update.Internal.d__39.MoveNext() 956 System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() 32 System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) 62 System.Data.Entity.Core.Objects.d__1561.MoveNext() 897 System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() 32 System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) 62 System.Data.Entity.Core.Objects.<SaveChangesToStoreAsync>d__154.MoveNext() 491 System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() 32 System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) 62 System.Data.Entity.SqlServer.<ExecuteAsyncImplementation>d__61.MoveNext() 472 System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() 32 System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) 62 System.Data.Entity.Core.Objects.d__151.MoveNext() 716

[DbUpdateException: An error occurred while saving entities that do not expose foreign key properties for their relationships. The EntityEntries property will return null because a single entity cannot be identified as the source of the exception. Handling of exceptions while saving can be made easier by exposing foreign key properties in your entity types. See the InnerException for details.] System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() 32 System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) 62 System.Runtime.CompilerServices.TaskAwaiter1.GetResult() 32 EsteticaWeb.Controllers.<Create>d__4.MoveNext() in C:\Users\Carlos Vidal\source\repos\EsteticaWeb\EsteticaWeb\Controllers\NegociosController.cs:93 System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() 32 System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) 62 System.Web.Mvc.Async.TaskAsyncActionDescriptor.EndExecute(IAsyncResult asyncResult) 92 System.Web.Mvc.Async.<>c__DisplayClass8_0.<BeginInvokeAsynchronousActionMethod>b__1(IAsyncResult asyncResult) 22 System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult) 42 System.Web.Mvc.Async.<>c__DisplayClass11_0.<InvokeActionMethodFilterAsynchronouslyRecursive>b__0() 80 System.Web.Mvc.Async.<>c__DisplayClass11_2.<InvokeActionMethodFilterAsynchronouslyRecursive>b__2() 396 System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethodWithFilters(IAsyncResult asyncResult) 42 System.Web.Mvc.Async.<>c__DisplayClass3_6.<BeginInvokeAction>b__4() 50 System.Web.Mvc.Async.<>c__DisplayClass3_1.<BeginInvokeAction>b__1(IAsyncResult asyncResult) 188 System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeAction(IAsyncResult asyncResult) 38 System.Web.Mvc.<>c.<BeginExecuteCore>b__152_1(IAsyncResult asyncResult, ExecuteCoreState innerState) 29 System.Web.Mvc.Async.WrappedAsyncVoid1.CallEndDelegate(IAsyncResult asyncResult) 73 System.Web.Mvc.Controller.EndExecuteCore(IAsyncResult asyncResult) 52 System.Web.Mvc.Async.WrappedAsyncVoid1.CallEndDelegate(IAsyncResult asyncResult) 39 System.Web.Mvc.Controller.EndExecute(IAsyncResult asyncResult) 38 System.Web.Mvc.<>c.<BeginProcessRequest>b__20_1(IAsyncResult asyncResult, ProcessRequestState innerState) 43 System.Web.Mvc.Async.WrappedAsyncVoid1.CallEndDelegate(IAsyncResult asyncResult) 73 System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) 38 System.Web.CallHandlerExecutionStep.InvokeEndHandler(IAsyncResult ar) 231 System.Web.CallHandlerExecutionStep.OnAsyncHandlerCompletion(IAsyncResult ar) 172

I hope you can help me please, I've been stuck on this for a long time.

CodePudding user response：

First of all, you are duplicating code, don't do it, but that's not the core issue.

The answer is pretty obvious

you don't have a table called "dbo.NegociosCategorias"

You have created a many to many relationship table using EF code first approach called "dbo.CategoriaNegocios", EF will create a joining table with the name of both entities and with the suffix s at the end.

You can change the name of this table by using Fluent API:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{

    modelBuilder.Entity<Negocio>()
                .HasMany<Categoria>(s => s.Categorias)
                .WithMany(c => c.Negocios)
                .Map(cs =>
                        {
                            cs.MapLeftKey("NegocioId");
                            cs.MapRightKey("CategoriaId");
                            cs.ToTable("NegociosCategorias");
                        });

}

This will change the name of the many to many table to "dbo.NegociosCategorias".

This is one way, the opposite way without changing the table name is to get the name right(obviously), I'm not sure how you end up with a table named "NegociosCategorias" as it should be "CategoriaNegocios" anyways.

I think you should really change the structure of your project, a domain, and a web, everything database related should remain in the domain project, and the web project just reference to the domain project, that way you don't have to duplicate the classes.

I mean it is already confusing to have one project:

public class Negocio {
    ...
    public virtual ICollection<Categoria> Categoria{ get; set; }
    ...
}

and another project:

public partial class Negocios {
    ....
    public virtual ICollection<Categorias> Categorias { get; set; }
    ....
}

and why is it even a partial class, you expecting more from other source files?