locked
how to join two tables with comma separated values column in c# linq asp.net mvc RRS feed

  • Question

  • User-932855619 posted

    I have two tables Profile and CourseList.

    Profile table:

    ID   | Name   | CourseId
    -----+--------+----------
     1   | Zishan |  1,2                                          
     2   | Ellen  |  2,3,4 

    CourseList table:

    courseid | coursename 
    ---------+--------------
       1     |  java
       2     |  C++
       3     |  oracle
       4     |  dot net

    Here I am joining the two tables and get results and that result in my View page for particular ID like...

    If I call ID = 1 in my view:

    Name: Zishan,

    course name: java,C++

    If if i call ID = 2 in my view:

    Name: Ellen,

    course name: java,C++,oracle.

    So I write this Linq query in my controller:

    var account = db.Profile.Where(x => x.PId == pid).FirstOrDefault();
    
    string  CourseIDS = string.Join(",",account.CourceId);
    
    var courselist = (from p in db.profile
                      join co in db.CourceList on p.CourceId equals co.courseId 
                      .ToString()  into co1 from co2 in co1.Where(x => 
                       LanguageIDS.Contains(x.courseId.ToString()))
                select new ViewProfileVM
                {
                   courseName = string.Join(",", co2.courseName)
                }).FirstOrDefault();
    
    ViewBag.courselist = courselist;

    Then I pass ViewBag to view and show results.....

    Please help me the linq query is not working and in future i want to add join LanguagesList and CountryList like same as CourceList so please suggest me a simple solution for this issue..

    Thanks,

    Monday, December 3, 2018 12:04 PM

All replies

  • User475983607 posted

    I recommend that you fix the table schema and add another table to handle the many-to-many relationship rather than using comma separated list.  As written queries against the Profile table can never be optimized.

    You can learn how to design relational database here.

    https://docs.microsoft.com/en-us/sql/relational-databases/tables/create-foreign-key-relationships?view=sql-server-2017

    https://docs.microsoft.com/en-us/sql/ssms/visual-db-tools/create-relationships-between-tables-on-a-diagram-visual-database-tools?view=sql-server-2017

    Monday, December 3, 2018 12:18 PM
  • User-932855619 posted

    Is this way to store comma separated values in one column in sql server table... please suggest me good approach. Create a new table to store course-list table using forginkey relationships or comma separated values in one column like (1,2,3).

    Monday, December 3, 2018 1:17 PM
  • User475983607 posted

    chanduram2020

    Is this way to store comma separated values in one column in sql server table... please suggest me good approach. Create a new table to store course-list table using forginkey relationships or comma separated values in one column like (1,2,3).

    See my previous link and read the documentation.  You really should fix the table design by learning relation database design.

    Monday, December 3, 2018 1:56 PM
  • User-474980206 posted

    sql is a set language. you store comma separated lists, as rows in a table.

    also, when using linq to sql, the C# code must be translated to sql, so many C# methods are not supported, like string.Join()

     

    Monday, December 3, 2018 3:44 PM
  • User753101303 posted

    Hi,

    No as pointed already the usual approach for a relational database is to have a 3rd table that holds a row for each possible (userId,courseId) couple. You can then use "join" to easily match users and courses.

    It may seem a bit more complex at first but overall it's easier to use. For example you can easily list all users taking java courses which is less obvious with a so called "multi-valued" column. You can also define referential integrity (ie you won't be able to create a couple that refers to a non existing courseId).

    Monday, December 3, 2018 3:55 PM
  • User1520731567 posted

    Hi chanduram2020,

    var courselist = (from p in db.profile
                      join co in db.CourceList on p.CourceId equals co.courseId 
                      .ToString()  into co1 from co2 in co1.Where(x => 
                       LanguageIDS.Contains(x.courseId.ToString()))
                select new ViewProfileVM
                {
                   courseName = string.Join(",", co2.courseName)
                }).FirstOrDefault();

    Unclear.This code makes me very confusing.

    But according to your description,I suggest you could refer to this article about one to many entity:

    http://www.entityframeworktutorial.net/code-first/configure-one-to-many-relationship-in-code-first.aspx

    Designing a table structure can make coding much easier.

    Best Regards.

    Yuki Tao

    Tuesday, December 4, 2018 9:00 AM
  • User-271186128 posted

    Hi chanduram2020,

    Since the CourseId value in the Profile and CourseList table are not same, I think you can't directly using the Join method to join the tables. 

    I suggest you could refer to the following code to query the related data.

            public ActionResult Index()
            {
                using (dbEntities db = new dbEntities())
                {
                    var pid = 2;
                    //get the special profile.
                    var account = db.Profiles.Where(x => x.ID == pid)
                        .Select(x => new ProfileVM() { ProId = x.ID, ProName = x.Name, CourseId = x.CourseId }).FirstOrDefault();
                    //split the courseid
                    var couserids = account.CourseId.Split(',');
                    //based on the courseid to get the related course name.
                    var cousernames = string.Join(",", db.CourseLists.Where(c => couserids.Contains(c.courseid.ToString()))
                        .Select(c => c.courseName).ToList());
                    //assign the coursename to the viewmodel.
                    account.CourseName = cousernames;
                    //then, you could use viewbag to display this viewmodel.
                }
                return View();
            }

    The result as below:

    If you want to use the Join method, as previous replies, you could configure the relationship between the Profile and CourseList Table (using foreign key).

    The table data like this:

    ID   | Name   | CourseId (foreign key)
    -----+--------+----------
     1   | Zishan |  1       
     1   | Zishan |  2                                      
     2   | Ellen  |  2 
     2   | Ellen  |  3 
     2   | Ellen  |  4 

    and

    courseid | coursename 
    ---------+--------------
       1     |  java
       2     |  C++
       3     |  oracle
       4     |  dot net

    In this scenario, you could use the Join clause to join the tables and get related values.

    Best regards,
    Dillion

    Thursday, December 13, 2018 6:22 AM