Queries from DataLoadOptions not optimized

[[email protected]]

Hello

I am using dotConnect 5.00.27 Beta. I was very pleased that within this release there are now the DataLoadOptions available.

I tried the following:

Code: Select all

Devart.Data.Linq.DataLoadOptions dataLoadOptions = new Devart.Data.Linq.DataLoadOptions();
dataLoadOptions.LoadWith(c => c.CountryTranslations);
dataLoadOptions.AssociateWith(c => c.CountryTranslations.Where(ct => ct.LanguageId == (int)Language));
MyDataContext.LoadOptions = dataLoadOptions;
MyDataContext.DeferredLoadingEnabled = false;

var recordSet = from c in MyDataContext.Countries
				join ct in MyDataContext.CountryTranslations on c.CountryId equals ct.CountryId
				where ct.LanguageId == (int)Language
				orderby ct.CountryName
				select c;

List countries = recordSet.ToList();

This loads all countries but the associated CountryTranslations table will be filled with the entries of only one language. Great this works! But unfortunately the generated SQL looks like this:

Code: Select all

SELECT t1.`CountryId`, t1.`CountryCode`, t1.ts
FROM tbl_countries t1
INNER JOIN tbl_country_translations t2 ON t1.`CountryId` = t2.`CountryId`
WHERE t2.`LanguageId` = 2
ORDER BY t2.`CountryName`

SELECT t1.`CountryTranslationId`, t1.`CountryId`, t1.`LanguageId`, t1.`CountryName`, t1.ts
FROM tbl_country_translations t1
WHERE (t1.`LanguageId` = 2) AND (3 = t1.`CountryId`)

SELECT t1.`CountryTranslationId`, t1.`CountryId`, t1.`LanguageId`, t1.`CountryName`, t1.ts
FROM tbl_country_translations t1
WHERE (t1.`LanguageId` = 2) AND (6 = t1.`CountryId`)

SELECT t1.`CountryTranslationId`, t1.`CountryId`, t1.`LanguageId`, t1.`CountryName`, t1.ts
FROM tbl_country_translations t1
WHERE (t1.`LanguageId` = 2) AND (59 = t1.`CountryId`)

SELECT t1.`CountryTranslationId`, t1.`CountryId`, t1.`LanguageId`, t1.`CountryName`, t1.ts
FROM tbl_country_translations t1
WHERE (t1.`LanguageId` = 2) AND (12 = t1.`CountryId`)

The generated code is querying my CountryTranslations table 300 times. This could be done in a single statement. Is it possible to fix this?

I also found out that DBMonitor doesn't correctly traces all those generated statements. Is this because of the specified DataLoadOptions?

Best regards
________
RESTLESS LEG SYNDROME FORUMS

[AndreyR]

Thank you for the report, the optimization is not available yet.
We are working on it, but no timeframe can still be specified.

[AndreyR]

The DataLoadOptions optimization is added in the current build of dotConnects.