locked
one to many relation ship RRS feed

  • Question

  • User2131862557 posted
    T-ID	T-name		Sh column
    ==================================
    1	T1		Sh1
    2	T1		Sh2
    3	T1		Sh3
    4	T2		Sh1
    5	T2		Sh2
    6	T2		Sh3
    7	T3		Sh2
    8	T3		Sh4

    here is sample data , where  T1 hold three records , and T2 holds 2 records and T3 also holds three records . I need to make one to many relationship between T-name and Sh Column in MVC . data in coming in reader (sql reader)

    reader gives 8 record and I need to filter records as  one to many relation ship between T-name and Sh-column so that I can bind in my view in html table (nested one). I have class so fat like this

    class my class

    {

    int T-ID

    string T-name

    }

    I am unable to map Sh-column in my class .

    Thursday, September 3, 2020 5:36 AM

All replies

  • User1686398519 posted

    Hi dua,

    According to your description, T and Sh have a one-to-many relationship. In other words, a T can have multiple sh.

    I made a simple example, you can refer to it.

    • To use Include in Entity Framework with lambda expressions, you need to add Import/using statements to include the System.Data.Entity namespace, otherwise the compiler will not find the extension method.
      • using System.Data.Entity;

    Model

        public class TModel
        {
            [Key]
            public int TID{get;set;}
            public string Tname{get;set; }
            public ICollection<ShModel> ShModels { get; set; }
        }
        public class ShModel
        {
            [Key]
            public int ShID { get; set; }
            public string Shname { get; set; }
            public int TID { get; set; }
            [ForeignKey("TID")]
            public TModel TModel { get; set; }
        }

    DailyMVCDemoContext

     public class DailyMVCDemoContext : DbContext
        {
            public DailyMVCDemoContext()
                : base("name=DailyMVCDemoContext")
            {
            }
            public virtual DbSet<ShModel> ShModels { get; set; }
            public virtual DbSet<TModel> TModels { get; set; }
        }

    Controller

        public class TController : Controller
        {
            public DailyMVCDemoContext db = new DailyMVCDemoContext();
            public ActionResult Index()
            {
                var sh = db.ShModels.Include(s => s.TModel).ToList();
                return View(sh);
            }
        }

    Index

    @model IEnumerable<DailyMVCDemo.Models.ShModel>
    <table class="table">
        <tr>
            <td>@Html.DisplayNameFor(m => m.ShID)</td>
            <td>@Html.DisplayNameFor(m => m.TModel.Tname)</td>
            <td>@Html.DisplayNameFor(m => m.Shname)</td>
        </tr>
        @foreach (var item in Model)
        {
            <tr>
                <td>@Html.DisplayFor(m => item.ShID)</td>
                <td>@Html.DisplayFor(m => item.TModel.Tname)</td>
                <td>@Html.DisplayFor(m => item.Shname)</td>
            </tr>
        }
    </table>

    Here is the result.

    Best Regards,

    YihuiSun

    Thursday, September 3, 2020 8:18 AM
  • User2131862557 posted

    Actually I need to fill via reader ,sh model ,how to do this?

    Thursday, September 3, 2020 8:55 AM
  • User1686398519 posted

    Hi dua,

    Do you mean you want to use SqlDataReader to read data? You can modify the code like this.

            public ActionResult Index()
            {
                List<ShModel> result = new List<ShModel>();
                string connectionString= ConfigurationManager.ConnectionStrings["DailyMVCDemoContext"].ConnectionString;
                using (SqlConnection connection =new SqlConnection(connectionString))
                {
                    string cmdText = "SELECT * FROM ShModels as s JOIN TModels as t ON t.TID = s.TID";
                    SqlCommand command = new SqlCommand(cmdText,connection);
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        ShModel item = new ShModel()
                        {
                            ShID = (int)reader["ShID"],
                            Shname = reader["Shname"].ToString(),
                            TID = (int)reader["TID"],
                            TModel = new TModel
                            {
                                TID= (int)reader["TID"],
                                Tname= reader["Tname"].ToString()
                            }
                        };
                        result.Add(item);
                    }
                }
                return View(result);
            }

    Best Regards,

    YihuiSun

    Thursday, September 3, 2020 9:26 AM
  • User2131862557 posted

    please note down that I need to bind in view as below

            T-name                    Sh column

    ==============================

                                                          Sh1

    T1                                                 Sh2

                                                        Sh3

    ==================================

                                                         Sh1

    T2                                                Sh2

                                                        Sh3

    ==================================

    I  think reader need to changed because there is one to many relationship as per above

                                                        Sh2

    T3                                                Sh4

                                                       

    ==================================

    Friday, September 4, 2020 5:03 AM
  • User1686398519 posted

    Hi dua,

    You can group and merge cells when the view is displayed without modifying the original SQL query statement.

    Below is the modified code, please refer to it.

    @model IEnumerable<DailyMVCDemo.Models.ShModel>
    <table class="table table-bordered">
        <tr>
            <td>@Html.DisplayNameFor(m => m.TModel.Tname)</td>
            <td>@Html.DisplayNameFor(m => m.Shname)</td>
        </tr>
        @foreach (var group in Model.GroupBy(m => m.TID))
        {
            for (int i = 0; i < group.Count(); i++)
            {
                var sh=group.ToList()[i];
                if (i == 0)
                {
                    <tr><td rowspan="+@group.Count()+">@sh.TModel.Tname</td><td>@sh.Shname</td></tr>
                }
                else
                {
                    <tr><td>@sh.Shname</td></tr>
                }
            }
        }
    </table>

    Here is the result.

    Best Regards,

    YihuiSun

    Friday, September 4, 2020 7:20 AM
  • User2131862557 posted

    that means we do not need to filter data in reader function .all can do in mvc view .?

    Friday, September 4, 2020 8:38 AM
  • User1686398519 posted

    Hi dua,

    Yes, you can do it directly on the view.

    Best Regards,

    YihuiSun

    Friday, September 4, 2020 10:24 AM
  • User2131862557 posted

    what if I need to print serial number as much rows are present

    Wednesday, September 9, 2020 1:09 PM
  • User1686398519 posted

    Hi dua,  

    1. According to your needs, I wrote an example for your reference.
      • @model IEnumerable<DailyMVCDemo.Models.ShModel>
        <table class="table table-bordered">
            <tr>
                <td></td>
                <td>@Html.DisplayNameFor(m => m.TModel.Tname)</td>
                <td>@Html.DisplayNameFor(m => m.Shname)</td>
            </tr>
            @{var j = 0; }
            @foreach (var group in Model.GroupBy(m => m.TID))
            {
                for (int i = 0; i < group.Count(); i++)
                {
                    var sh = group.ToList()[i];
                    if (i == 0)
                    {
                        <tr><td rowspan="+@group.Count()+">@(j+1)</td><td rowspan="+@group.Count()+">@sh.TModel.Tname</td><td>@sh.Shname</td></tr>
                    }
                    else
                    {
                        <tr><td>@sh.Shname</td></tr>
                    }
                }
                if (j < Model.GroupBy(m => m.TID).Count())
                {
                    j++;
                }
            }
        </table>
    2. Has your previous problem been solved?I suggest you could mark the answer which is helpful. This will help other people who faces the same issue to find the right answer faster.
    3. If you have a new question, you need to post a new post, which can help you better.

    Best Regards,

    YihuiSun

    Thursday, September 10, 2020 2:25 AM