Exception: "Joins can only involve base type or reference fields"

[john]

I have a exception "Exception: "Joins can only involve base type or reference fields" while running following code:
using (var context = new MaintainRuleDA())
            {
                var source = from p in context.Functions
                             join t in context.FunctionFields on p.FunctionId.ToString() equals t.FunctionId
                             where !t.IsDeleted && !p.IsDeleted && t.FunctionId == functionId
                             select p.FunctionId;


                return source.ToList().Count > 0;
            }

Note: The Functions.FunctionId  is a guid type and FunctionFields.FunctionId is string type in DB

[john]

Any solution?

[john]

Can help to check above issue?

[Viktor Zhivkov]

Hello,

The problem you are experiencing is caused by the .ToString() method call in the join clause.
Currently OpenAccess does not support join clauses that involve anything other than simple property/field access.
The only way to fix the issue is to change the type of the key columns used in the join. They should have the same type.
Given the fact that now the relation model does not have a relation between Function and FunctionField tables and columns used for the join are not the same type, you will suffer big performance issues if you do the join even by native SQL statements. So my suggestion is to change the type of the FunctionField.FunctionId to GUID and if possible add a relation between the two tables.

A few more suggestions for improvements of the query you have posted:

• add the more restrictive filters first in the where clause. In this particular case it means to move t.FunctionId == functionId to be the first filter clause
• if you want to count the row it is better to count them on server and transport single integer value as opposed to hundreds and thousands of rows and then counting them in memory. To fix this you can use source.Count() extension method.

If you have any additional question do not hesitate to contact us in the forums or using our dedicated customer support system.

Greetings,
Viktor Zhivkov
the Telerik team

[john]

Hi Viktor,

Thanks for you good suggestion. For your "add the more restrictive filters first in the where clause". I don't understand why? Could you help to explain more clearly?


I also have a design issue. For example, I have two table: Function and Function Field. Function table will have two field: Function Name and Function Id. Function Field table have three fields:Field Name, Function Id. Field Id. I generate two entity classes with OpenAccess ORM. While I binding Filed table to RadGrid, it will show Field Name and Function Name. My question is how to return a list which contain Field Name and Function Name with OpenAccess ORM API? I don't want to create third class to include the Field Name and Function Name because it's only for search purpose.

Thanks,
Zhihao

[Viktor Zhivkov]

Hello Zhihao,

Talking about filters and their priority.
If you have several hundred rows in a table with structure similar to the one in you case:
Table A, columns Id (primary key), Name, IsDeleted and ForeignKeyId.
You would like to apply the filter that will return the minimum number of rows first.
This should mean:

1. Id - queries by primary key return 0 or 1 results
2. ForeignKeyId - depending on you scenario this can return 0, 1..100 or more rows, but in general a small fraction of all rows.
3. Name - same as ForeignKeyId, but in general same name rarely is used more than several times. The reason why it is better to filter first on ForeignKeyId is because usually there is an index that speeds up the query significantly. Not that you cannot add index on Name anyway.
4. IsDeleted - depending on the situation this can return 0, 20%, 50% or 100% of the rows in the table. So it is better to apply such filter after the working set is reduced to a more manageable size using stronger filters.

You have several options for your second question:

1. You can use System.Tuple<FunctionField, string> instead of hand written class.
2. You can use anonymous class, but I do not recommend to use it for binding if you are following MVVM pattern.
3. Load both rows for FunctionField and Function in memory. If you have a relation defined in the database or an association defined in the OpenAccess conceptual model in the RLINQ file, you can load the related object using a Fetch Strategy and then display it easily in your grid. You can see examples how to do so in OpenAccess SDK SofiaCarRental samples. Just pick the one that uses similar technology to the one you are working with. Check out how Available Cars grid is bound and how the category of the car is shown in the grid. This demonstrates exactly the same scenario that you are describing.

If you need any additional assistance, please open a new support ticket in our Customer Support system or start a new forum thread for your new question.

Kind regards,
Viktor Zhivkov
the Telerik team

[john]

Hi,

For your first option, how can I bind the object into RadGrid?
For your third options about my design issue, could u give more detailed explain or code because I cannot get the similar code as your described in SofiaCarRental samples?

Thanks,
Zhihao

[Viktor Zhivkov]

Hello Zhihao,

About option 1: You can use normal binding syntax - Tuple type behaves just like normal type. The only difference is that it's properties are called Item1, Item2 and so on. These properties are strongly typed.

About your next question: please share with us which front-end technology are you using in your project, because binding in ASP.NET is very different than the one in WPF/Silverlight.

Regards,
Viktor Zhivkov
the Telerik team

[john]

I'm using ASP.NET as front-end technology.

[Viktor Zhivkov]

Hello Zhihao,

You can find many examples of working projects that demonstrate how to integrate OpenAccess and some other popular technologies in OpenAccess SDK. Yout can download the SDK from http://www.telerik.com/products/orm/features/sdk.aspx.

After installing it you can take a look at ASP.NET -> Sofia Car Rental  ASP.NET Ajax example.
There you can find AvailableCarsGrid.ascx under SofiaCarRenta.Web/Views.
The situation there is very similar to the one you are trying to implement: each Car entity has a Category.
The grid in the example shows that category and allows users to group and sort the whole grid by this column.
The code to bind the grid in the .ascx file is pretty simple (I have omitted the unnecessary details):

01.<telerik:RadGrid ID="rgCars" runat="server" ShowGroupPanel="True"
02.                AllowPaging="true" DataSourceID="ldsCars" AllowSorting="true">
03.                <MasterTableView AutoGenerateColumns="false" OverrideDataSourceControlSorting="true"
04.                    ClientDataKeyNames="CarID, Make">
05.                    <PagerStyle Mode="NextPrevAndNumeric" ShowPagerText="false" PageSizeLabelText="" />
06.                    <GroupByExpressions>
07.                        <telerik:GridGroupByExpression>
08.                            <SelectFields>
09.                                <telerik:GridGroupByField FieldAlias="Category" FieldName="Category.CategoryName" FormatString="{0:D}">
10.                                </telerik:GridGroupByField>
11.                            </SelectFields>
12.                            <GroupByFields>
13.                                <telerik:GridGroupByField FieldName="Category.CategoryName" SortOrder="Descending"></telerik:GridGroupByField>
14.                            </GroupByFields>
15.                        </telerik:GridGroupByExpression>
16.                    </GroupByExpressions>
17.                    <Columns>
18.                        <telerik:GridBoundColumn DataField="CarID" UniqueName="CarID" Visible="false">
19.                        </telerik:GridBoundColumn>
20.                        <telerik:GridBoundColumn DataField="Category.CategoryName" UniqueName="Category" HeaderText="Category">
21.                        </telerik:GridBoundColumn>
22.                        <telerik:GridBoundColumn DataField="Make" UniqueName="Make" HeaderText="Make">
23.                        </telerik:GridBoundColumn>
24.                        <telerik:GridBoundColumn DataField="Model" UniqueName="Model" HeaderText="Model">
25.                        </telerik:GridBoundColumn>
26.                    </Columns>
27.                </MasterTableView>
28.                <ClientSettings EnableRowHoverStyle="true">
29.                    <Selecting AllowRowSelect="true" />
30.                    <ClientEvents OnRowSelected="RowSelected" />
31.                </ClientSettings>
32.            </telerik:RadGrid>

The column binding is done on line 20. 
Lines between lines 6 and 16 define how to group the items in the grid using again the Category column.
Grid's DataSource is set to an instance of OpenAccessLinqDataSource (DataSourceID="ldsCars" 
) on line 2. 
The DataSource itself is defined using the following code:

1.<telerik:OpenAccessLinqDataSource runat="server" ID="ldsCars"
2.        OnSelecting="ldsCars_OnSelecting"
3.        OrderBy="MakeAndModel"/>

Later the data source is populate through a Web Service call. In the server side code you can find how the data is loaded. Look at SofiaCarRental.Model/Services/CarsService.cs:

01.public virtual IList<Car> ReadAvailableCarsForPeriod(DateTime startDate, DateTime endDate)
02.{
03.    if (endDate < startDate)
04.    {
05.        throw new InvalidOperationException(string.Format("The given period '{0}' - '{1}' is not valid.",
06.            startDate.ToString(),
07.            endDate.ToString()));
08.    }
09.
10.    return this._carsRepository.GetAll().Where(c =>
11.        c.RentalOrders == null ||
12.        c.RentalOrders.Count == 0 ||
13.        !c.RentalOrders.Any(o =>
14.            (o.RentStartDate > startDate && o.RentStartDate < endDate) ||
15.            (o.RentEndDate > startDate && o.RentEndDate < endDate))).ToList();
16.}

As you can see from the code above we are querying using LINQ extension methods for data and then returning it as a List<Car>.

I believe the hardest link to implement is the creation of the OpenAccessLinqDataSource object. You can find more information at http://www.telerik.com/help/openaccess-orm/developer-guide-oalinqdatasource-overview.html.

I believe you can translate the example for your scenario and use the same technique to bind FunctionFields and Functions to a grid.
I hope that this will clear any issues you have faced and will enable you to complete successfully your project using OpenAccess.

Kind regards,
Viktor Zhivkov
the Telerik team