locked
one to many relationship RRS feed

  • Question

  • User-180482348 posted

    I have two tables: fotoblock and transport. They have one-to-many relationship.One row in transport table may have zero or one or many rows in fotoblock table.

    Create table fotoblock(guid uniqueidentifier,img_name varchar(50)) insert fotoblock (guid,img_name) values('F6E4DB53-05D6-4761-8C89-965A5B13C1DD','4.jpg'), ('52CBF4D5-0713-492A-9F4E-5D1850C485F8','5.jpg'),('52CBF4D5-0713-492A-9F4E-5D1850C485F8','12.jpg') create table transport (tr_num varchar(10), calendar datetime, ID uniqueidentifier, empty_weight int); insert into transport values ('h151k', '2019-10-05T04:05:34.000', 'F6E4DB53-05D6-4761-8C89-965A5B13C1DD', 2100), ('k141n', '2019-12-05T07:35:08.000', '52CBF4D5-0713-492A-9F4E-5D1850C485F8', 1780), ('b898n', '2019-11-06T02:05:17.000', 'FC63FBA0-8A5D-4568-8FC0-4BB6F31ED290', 3150), ('h152k', '2019-10-05T04:05:34.000', 'F6E4DB53-05D6-4761-8C89-965A5B13C1DD', 4000), ('k142n', '2019-12-05T07:35:08.000', '52CBF4D5-0713-492A-9F4E-5D1850C485F8', 3270), ('a909z', '2019-07-08T05:00:02.000', 'AA7AE5E8-885F-4DE4-A3F7-587E1CC54B8E', 2990)

    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Linq;
    using System.Threading.Tasks;
    
    namespace Browse.Models
    {
        public class Transport
        {
            [Key]
            public Guid ID { get; set; }
            public string tr_num { get; set; }
            public DateTime calendar { get; set; }
            public int? empty_weight { get; set; }
           
            [ForeignKey("guid")]
            public ICollection<Fotoblock> Fotoblocks { get; set; }
            
        }
    }
    sing System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.Linq;
    using System.Threading.Tasks;
    
    namespace Browse.Models
    {
        public class Fotoblock
        {
            [Key]
            public Guid guid { get; set; }
            public string img_name { get; set; }
            public Transport Transport { get; set; }
        }
    }
    public async Task<IActionResult> Details(Guid? id)
            {
                if (id == null)
                {
                    return NotFound();
                }
    
                var transport = await _context.transport
                    .Include(z=>z.Fotoblocks)
                    .AsNoTracking()
                    .Where(m=>m.ID==id)
                    .ToListAsync();
                if (transport == null)
                {
                    return NotFound();
                }
    
                return View(transport);
            }
    using System.Linq;
    using System.Threading.Tasks;
    
    namespace Browse.Data
    {
        public class TransportContext:DbContext
        {
            public TransportContext(DbContextOptions<TransportContext> options)
                : base(options)
            {
            }
    
            public DbSet<Transport> transport { get; set; }
            public DbSet<Fotoblock> fotoblock { get; set; }
    
            protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
                modelBuilder.Entity<Transport>().ToTable("transport");
                modelBuilder.Entity<Fotoblock>().ToTable("fotoblock");
                
            }
        }
    }
    @model   IEnumerable<Browse.Models.Transport>
    @{
    ViewData["Title"] = "Details";
    }
    
    <h1>Details</h1> 
    
    <div>
        <h4>Transport</h4>
        <hr />
        <dl class="row">
    
            
    
          @foreach(var i in Model)
                
                   @foreach(var z in i.Fotoblocks)
                   {
                       @z.img_name
                   }
                
            
    
    
    
    
        </dl>
    </div>
    <div>
        
        <a asp-action="Index">Back to List</a>
    </div>

    Transport Row 'k141n', '2019-12-05T07:35:08.000', '52CBF4D5-0713-492A-9F4E-5D1850C485F8', 1780 corresponds with two fotoblock rows ('52CBF4D5-0713-492A-9F4E-5D1850C485F8','5.jpg'),('52CBF4D5-0713-492A-9F4E-5D1850C485F8','12.jpg'). In "Details" view I want to see '5.jpg' '12.jpg',but I see only '5.jpg'. How to fix the problem?

    Saturday, August 1, 2020 4:49 PM

Answers

All replies

  • User1535942433 posted

    Hi Decompressor,

    Accroding to your description and codes,I create a test.Could you tell us how you create table and insert data?In the table Fotoblock,you create the guid as a key.But you have two same data with the guid in the Fotoblock table.

    I suggest you could change the key of the Fotoblock table.The model just like this:

    [5:11 PM] Rena Ni (Shanghai Wicresoft Co,.Ltd.)
        
    public class Transport
    {
        [Key]
        public Guid ID { get; set; }
        public string tr_num { get; set; }
        public DateTime calendar { get; set; }
        public int? empty_weight { get; set; }
    
    
        [ForeignKey("guid")]
        public ICollection<Fotoblock> Fotoblocks { get; set; }
    
    
    }
    public class Fotoblock
    {
        public int FotoblockId { get; set; }
        public Guid guid { get; set; }
        public string img_name { get; set; }
        public Transport Transport { get; set; }
    }
    

    Best regards,

    Yijing Sun

    Monday, August 3, 2020 9:18 AM
  • User-180482348 posted

    I create the same Guid in two rows by hand, because it's demo version specially for this post. But I have the same problem in my enterprise with big data volume. Problem is that  table "transport" one row corresponds many rows in table "fotoblock". I need in "Details" page to show all

    rows with imageTitle from table "fotoblock" related by the equal guid to one row in table "transport". I don't understand how creating  column "int fotoblock id" can help me

    Monday, August 3, 2020 10:28 AM
  • User753101303 posted

    Hi,

    The problem is that you told EF that fotoblock.guid is the "primary key" while it is actually the "foreign key". So yij sun suggestion is to add a true primary key column into your database to fix this.

    Also you shouldn't be able to enter two row with the same ID in the Transport table.

    When EF is loading the 2nrd row, it sees it has already a row from the same table with the same primary key value and so this 2nd row is likely just ignored as the "primary key" is supposed to be unique.

    To understand pk vs fk see perhaps https://www.dotnettricks.com/learn/sqlserver/difference-between-primary-key-and-foreign-key#:~:text=Primary%20key%20uniquely%20identify%20a,primary%20key%20in%20another%20table.&text=By%20default%2C%20Primary%20key%20is,the%20sequence%20of%20clustered%20index.

    Edit: also the pk is liekly not well defined on the db side else it should block creating two rows with the same pk which is what you have done for both Transport and fotoblock. See also :
    https://docs.microsoft.com/en-us/sql/relational-databases/tables/create-primary-keys?view=sql-server-ver15
    https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-ver15 (allows to autonumbering int id columns)

    You can stiull use a Guid for your primary column if you prefer (except that you'll have to assign a value for newly inserted rows yourself).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 3, 2020 11:33 AM
  • User-180482348 posted

    Thank you very much. It helped!

    Monday, August 3, 2020 12:45 PM