Linq to Sql : how to get data - one to many

Question

My app in ASP.NET C#. I use Linq to Sql, look the picuture

// 5 data talbe :

// Unit table

     id |   name    | unit_type_id          Unit_type   
       -------------------------                    
        1 | Unit 1  |     1                     id   |  name
        2 | Unit 2  |     1                     -------------   
        3 | Unit 3  |     1                     1    |  Type 1
        4 | Unit 4  |     2                     2    |  Type 2
        5 | Unit 5  |     1                     3    |  Type 3
        6 | Unit 6  |     3     




 //Tool table                                // tool_type   table

    id  |   name    | tool_type_id              id   |  name
    ----------------------------            ------------------- 
    1   |   Tool 1  |     1                     1    |  Tool_type 1
    2   |   Tool 2  |     2                     2    |  Tool_type 2
    3   |   Tool 3  |     1                     3    |  Tool_type 3
    4   |   Tool 4  |     2         
    5   |   Tool 5  |     3 

//unit_tool table

unit_id | tool_id
    ---------------
    1   |    1
    2   |    1
    1   |    2
    1   |    3
    2   |    2
    2   |    3
    3   |    3
    3   |    2

I want to use linq to sql in C# to get data like

// Table data i want to get

Unit name  |  Unit type name  | Tool name
-------------------------------------------------
Unit 1     | Type 1           | tool 1, tool 2, tool 3
Unit 2     | Type 1           | tool 1, tool 2, tool 3
Unit 3     | Type 1           | tool 2, tool 3
Unit 4     | Type 2           | 
Unit 5     | Type 1           | 
Unit 6     | Type 3           | 

My code be low

// Create new class

public class unit_list
    {
        //Unit name
        public string unit_name { get; set; }
        // Unit type name
        public string unit_type_name { get; set; }
        // Tool name
        public string tool_name { get; set; }
    }

// Function get data

protected void show_unit()
{
    // Create datacontext
    for_testDataContext db = new for_testDataContext();
    //Query data
    IQueryable<unit_list> CourseList = from cl in db.units
                                        select new unit_list()
                                        {
                                            unit_name = cl.name,
                                            unit_type_name = cl.unit_type.name,
                                            tool_name = // how to get data like "tool1, tool 2, tool 3"
                                        };

}

Answer 1

You don't have any foreign keys set up, which means you don't get automatic "navigation properties" with your mapped entities. You will have to either update your database and then regenerate the mapping, or add them yourself using the toolbox.

EDIT:

Okay, let's run down a very short checklist.

1. Are primary and foreign keys set and associated properly in the schema (your database)? This is important, as this is what the sqlmetal executable uses to generate your object context.
2. Do the associations in your model point to the correct properties in the parent and child entities? You can check this by clicking on an association between two objects in the model viewer and then expanding the Properties pane. Expand the Child and Parent properties and verify they are not empty.

If you've done everything right so far, clicking on the association between Unit_Tool and Unit should show a Child property called Units.

If you're missing an association, you can add one by:

1. Clicking on the Association tool in the Toolbox.
2. Clicking on the object which defines the primary key.
3. Clicking on the object that holds the foreign key.

This will bring up a dialog box that should be pretty self-explanatory.

HTH.

EDIT:

Solved this by playing around with your existing code (thanks for the upload). The problem was super simple, but not exactly obvious: you need to have an identifier column in each table, or Linq-to-SQL doesn't create the child collection reference in the data context. So, since you're using unit_tool as just a pivot table (perfectly acceptable), without a unique ID on each row, you don't get a "navigation property." To fix this, just add a primary key field (you don't ever have to actually use it - it's just for the ORM) to the unit_tool table, recreate the entity mapping, and viola!, your query will work as-is.

I have to say, this wasn't obvious at first blush. I haven't ever had this problem, since I always create a unique identifier out of habit...

Answer 2

You should be able to write something like this (assuming all the relationships are set up correctly in Linq):

select new unit_list()
{
    unit_name = cl.name,
    unit_type_name = cl.unit_type.name,
    tool_name = string.Join(", ", 
        cl.unit_tools.Select(ut => ut.tool.name).ToArray()
    )
};

In other words..

1. Get the one-to-many relationship to unit_tools
2. Select the corresponding row in tool and select the name column
3. Join to get a single comma-separated string