CODEWITHSUNDEEP
c#linq
Mike
Mike

Reputation: 14584

LINQ query for multiple join tables in C#

In my application, I have to write a query that displays data from a number of the tables. In other words, I have to create a query that will make a join for number of tables and will show wanted information from this query. The query has to display all the events (and all data about the events in DataGrid) for a specific (chosen) worker.

Here is a code of my LINQ:

IList dataList = (from dWorker in App.glidusContext.tbl_workers
    where dWorker.workerTZ == strSplit
    join d2 in App.glidusContext.tbl_workers_has_tbl_events
    on dWorker.workerID equals d2.workerID
    join dEvent in App.glidusContext.tbl_events
    on d2.eventID equals dEvent.eventID
    join dAct in App.glidusContext.tbl_activities
    on d2.eventID equals dAct.eventID
    select new { d2.damagedVacantionEnd, dEvent, dAct }).ToList();

return dataList;

Where strSplit is a specific (chosen) worker.

XAML-code of DataGrid:

<DataGrid.Columns>
    <DataGridTextColumn Header="{x:Static res:Resources.WinSafetyByEmployee_DataGrid_ColHead_Event_type}"
        Width="130"
        IsReadOnly="True"
        Binding="{Binding Path=dEvent.tbl_eventsType.eventTypeName}" />
    <DataGridTextColumn Header="{x:Static res:Resources.WinSafetyByEmployee_DataGrid_ColHead_Classification}"
        Width="130"
        Binding="{Binding Path=dEvent.tbl_eventsClassification.eventClassificationName}" />
    <DataGridTextColumn Header="{x:Static res:Resources.WinSafetyByEmployee_DataGrid_ColHead_Catagory}"
        Width="130"
        Binding="{Binding Path=dEvent.tbl_eventsCategories.eventCategoryName}" />
    <DataGridTextColumn Header="{x:Static res:Resources.WinSafetyByEmployee_DataGrid_ColHead_Evacuation}"
        Width="130"
        Binding="{Binding Path=d2.damagedEvacuationDescription}" />
    <DataGridTextColumn Header="{x:Static res:Resources.WinSafetyByEmployee_DataGrid_ColHead_EvacuationStart}"
        Width="130"
        Binding="{Binding Path=d2.damagedVacantionStart}" />
    <DataGridTextColumn Header="{x:Static res:Resources.WinSafetyByEmployee_DataGrid_ColHead_EvacuationEnd}"
        Width="130"
        Binding="{Binding Path=d2.damagedVacantionEnd}" />
    <DataGridTextColumn Header="{x:Static res:Resources.WinSafetyByEmployee_DataGrid_ColHead_ActivityName}"
        Width="*"
        Binding="{Binding Path=dAct.activityName}" />
    </DataGrid.Columns>
</DataGrid>

How to fix this query? Now this variant doesn't return values.

I also cannot reach tbl_objects in the query because I need to do it thought the connection table tbl_object_has_tbl_events and App.glidus do not show such a table.

Here is an ERD of my database:

DB ERD

Upvotes: 1

Views: 2505

Answers (1)

Geoff Appleford
Geoff Appleford

Reputation: 18832

Try something like this (I'm not sure exactly what your navigation properties will be called) :

IList dataList = (from we in App.glidusContext.tbl_workers_has_tbl_events
                  where we.worker.workerTZ == strSplit
                  select new { we.damagedVacantionEnd, 
                               we.tbl_event, 
                               we.tbl_event.tbl_activity}).ToList();

return dataList;

Update:

Also, looking at you xaml, you are trying to bind to properties that aren't returned in you anonymous type. eg d2.damagedVacantionStart - you have not returned the entire d2 object so cannot bind to other properties on it, not have access to d2.

Try simplifying things by returning the exact properties you need in your query and binding directly to those. eg

select new { VacStart = we.damagedVacantionStart, 
             VacEnd = we.damagedVacantionEnd, 
             EvTypeName = we.tbl_event.tbl_eventsType.eventTypeName, 
             ...}).ToList();

Then in your xaml you can have simple binding expressions:

Binding="{Binding Path=evTypeName}" />

Upvotes: 2

Related Questions