Join multiple tables / data types when querying data with C# and LINQ

In C1 CMS you can use data reference fields in data types to reference items of one type in the other. When you get data items with the Get<DataType>Xml function, you can filter these items by this reference field.

For example, you have a data type for events which references a data type for months. You can thus filter data items of the "events" type by month when using the Get<DataType>Xml function.

What if your setup is more complex than that? Say, you also have a data type for quarters (3-month periods), and the "months" data type references this data type in its "quarter" field.  And what if you still want to get the "events" - but this time filtering them by quarter? In this case, using the Get<DataType>Xml function - as it is - will not work because you can only go one level down in referencing and thus filter by month.

You can still get to filter by quarter if you use C# and LINQ to build a proper query. You can use one of the following approaches:

Approach 1

You should create a "complete" function that does the querying and returns the XML you need, similar to the Get<DataType>Xml function. With this approach you are free to get data and query any number of tables.

• Read more about creating a custom Get<DataType>Xml function

Approach 2

You should create a filter function for getting "events" by quarter and use it in the Filter parameter of the corresponding Get<DataType>Xml function. With this approach, you can keep on using automatically generated the Get<DataType>Xml function for your "events" data type and make use of a more advanced filter you've made.

• Read more about creating a filter function for the standard Get<DataType> Xml function

For our "events by quarter" example, each approach will use some sample data types and data described below. You are free to customize and adapt them to your needs.

You can also download the demo package for both approaches.

• Download the sample demo

Sample data types

The above approaches use these sample data types and data.

Data type: Demo.Quarter

• Fields: Name (String, 16)
• Data items: I, II, III, IV

Data type: Demo.Month

• Fields: Name (String, 16); Quarter (Data Reference: Demo.Quarter)
• Data items: Jan, I; Feb, I; Mar, I; Apr, II; May, II; Jun, II; Jul, III; Aug, III; Sep, III; Oct, IV; Nov, IV; Dec, IV

Data type: Demo.Event

• Fields: Name (String, 32); Month (Data Reference: Demo.Month)
• Data items: (your choice but make sure add events to different months and this quarters)