Relations between tables - ASP.NET CORE MVC

Question

User-1925037784 posted

Hi,

 I need your help again. I have a project in ASP.NET that I have to read data and put this data in a database.

This is done.  The problem now is to relate these two tables.

I already made the relations in SQL Management Studio, but here in the project they don't work.

Explanation of the project:

One product can have more than one description.

So the descriptions must be in a different table

Now I will put the examples:

XML:

<products>

<product>

<id>100</id>

<name>Ball</name>

<price>15</price>

<quantity>2</quantity>


<description>

<comment>aaa</comment>

</description>

<description>

<comment>bbb</comment>

</description>
</product>
</products>

MODEL:

[Table("Table")]
    public class Product
    {
  
   
        public int Id { get; set; }
        public string Name { get; set; }
        public int Price { get; set; }
        public int Quantity { get; set; }
        
    }
   [Table("Comentario")]
   public class Comment
    {
        [Key]
	public int Id {get;set;}

        public string DescriptionComment { get; set; }
    }

CONTROLLER:

private List<Product> ProcessImport(string path)
        {
            XDocument xDocument = XDocument.Load(path);
            List<Product> products = xDocument.Descendants("product").Select
                (p => new Product()
                {
                   Id = Convert.ToInt32(p.Element("id").Value),
                   Name=p.Element("name").Value,
                   Quantity = Convert.ToInt32(p.Element("quantity").Value),
                   Price = Convert.ToInt32(p.Element("price").Value),
            
                }).ToList();
            
             List<Comment> comments = xDocument.Descendants("description").Select
                (t => new Comment()
                {
                  DescriptionComment= t.Element("description").Value
            
                }).ToList();
            
            
            foreach(var product in products)
            {
                var productInfo = db.Products.SingleOrDefault(p => p.Id.Equals(product.Id));
                if (productInfo != null)
                {
                    productInfo.Id = product.Id;
                    productInfo.Name = product.Name;
                    productInfo.Quantity = product.Quantity;
                    productInfo.Price = product.Price;
                }

                else 
                { 
                   db.Products.Add(product);
                }

                db.SaveChanges();
            }
             foreach (var comment in comments)
            {
                var commentInfo = db.Comments.SingleOrDefault(t => t.Id.Equals(comment.Id));
                if(commentInfo != null)
                {
                    commentInfo.Id = comment.Id;
                    commentInfo.DescriptionComment = comment.DescriptionComment;
                    
                }
                else
                {
                    db.Comments.Add(comment);
                }
            }
            return products;

            
        }

Does anyone have any idea how this is done?

Thanks for help me!

Answer 1

User-474980206 posted

It not enough to define the relationship in the database, you must also configure ef core to know about it. Read docs

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

Answer 2

User-121299044 posted

Yo estaba igual de perdido, pero me tomé un tiempo para leer un libro llamado "Mastering Entity Framework Core 2" y me ha funcionado, como sea aquí alguna luz...

Basically you have 2 tables, Products and Comments... the first thing you must think is:

1. what kind of relationship is this? one to one? one to many? many to many? In your case, I think you have 1 product with Many comments = one to many.
2. who is the main entity and the dependent entity? the main is Product and the dependent is Comments.
3. You want to do the relationship using Fluent API? or on the table?

[Table("Table")]
    public class Product
    {  
        public int Id { get; set; }
        public string Name { get; set; }
        public int Price { get; set; }
        public int Quantity { get; set; }   
public virtual IList<Comment> Comments {get; set;}

    }
   
[Table("Comentario")]
   public class Comment
    {
	public int Id {get;set;}
        public string DescriptionComment { get; set; }

public int ProductId {get; set;}
[ForeignKey("ProductId")]
public Product Product{get; set;}

    }

If you check the code above you will notice this: In order to get a one-to-many relationship, the MAIN entity must have a property which is a List of the DEPENDENT entity, the virtual keyword is there to allow "lazy loading", but is not required.

On the DEPENDENT entity (in this case Comment) you must create a reference to a single MAIN entity by specifying what the key of the MAIN entity is, and a Navigation Property (a reference to the item itself) even though it is not required. (the Navigation Property) and vuala!

public int ProductId {get; set;}
[ForeignKey("ProductId")]
public Product Product{get; set;}

//could be...

public int MyIdProduct {get; set;}
[ForeignKey("MyIdProduct")]
public Product Product{get; set;}

The foreignkey attribute [ForeignKey("")] IS NOT required, as per design EF understand if the field name is ProductId, so you don't have to specify that explicitly, but in case the field where the ProductID is called something weird like  "MyIdProduct" then you can use foreignkey to say something like "the ID for the Product entity is called MyProductId", on the comments table, I should clarify, NOT in the main entity, as we know in the main entity is called ID.

DONT FORGET to create a migration and move this to the DB, EF will create the relationships for you, you don't have to go to SQL server to try to do this manually... Hope this can help!

Answer 3

User-217098811 posted

<trans oldtip="你好和5ilva" newtip="你好和5ilva">Hi andre5ilva</trans>

As these members have said, you can configure table relationships by using EFCore.

It can be seen that your product and comment should be one to many. In addition to sipi.perez@g..。's method configuration,

you can also configure them through the Fluent API method. Here is the code configured through the fluent API

Product class

public class Product
    {  
	public Product()
	{
	Comments=new List<Comment>();
	}
        public int Id { get; set; }
        public string Name { get; set; }
        public int Price { get; set; }
        public int Quantity { get; set; }   
	public virtual IList<Comment> Comments {get; set;}
    }

Comment class

   public class Comment
    {
	public int Id {get;set;}
        public string DescriptionComment { get; set; }
	public int ProductId {get; set;}
	public Product Product{get; set;}
    }

Your context

public class MyContext:DbContext
    {
        public MyContext(DbContextOptions<MyContext> options):base(options)
        {
        }
 	protected override void OnModelCreating(ModelBuilder modelBuilder)
        { 
	modelBuilder.Entity<Comment>()
	.HasOne(x => x.Product)
	.WithMany(x => x.Comments)
	.HasForeignKey(x => x.ProductId);
	}
	public DbSet<Product> Products { get; set; }
	public DbSet<Comment> Comments { get; set; }
    }

Inject in Startup 

services.AddDbContext<MyContext>(options =>
            {
                options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection"));
            });

Finally, migrate and update in PMC

Add-Migration Inicial

update-database

Hope this can help you

Best regards

yinqiu

Answer 4

User-217098811 posted

<trans oldtip="Hi andre5ilva" newtip="你好和5ilva">Hi andre5ilva</trans>

I'm glad I can help you

Best regards

yinqiu