Devart Forums

Idsa

Glad to hear. But looks like I am going back to MySQL .NET Connector. After they have fixed some critical bug, it looks reasonable for me: according to your license agreement, I have no idea how to combine my open source projects and your providers.

Idsa

Hm... I have found out PM is disabled here. Please write me to [email protected] and I will send you connection string.

Idsa

2. I'm too lazy for that

I would better provider you connection string to my test server. You can generate a model, look at indexes, etc. I will send it to you by email. I hope you won't modify there anything.

3. I have provided you what query MySQL .NET Connector generates for the same lambda. And this query works very efficient (and looks reasonable). So I guess you should generate something similar

4. In my opinion, it should be query like

select path from categories where categoryId = 43 limit 2

I don't see any reason for nested query here

Idsa

The simplest query:

return ctx.Categories.Where(c => c.Id == categoryId).Select(c => c.Path).Single();

results in:

SELECT Limit1.path AS path
FROM (SELECT Extent1.path AS path
FROM ef.categories AS Extent1
WHERE Extent1.id = 43 /* @p__linq__0 */
LIMIT 2) AS Limit1

Idsa

One more very inefficient query.

Here is lambda:

ctx.Items.
Include("ItemCategories.Category").
Where(i => itemsIds.Contains(i.Id)).
ToList();

and here is a generated SQL:

SELECT Project1.C1 AS C1,
Project1.devTitle AS devTitle,
Project1.id AS id,
Project1.isAutogenerated AS isAutogenerated,
Project1.C2 AS C2,
Project1.CategoryId AS CategoryId,
Project1.id1 AS id1,
Project1.ItemId AS ItemId,
Project1.description AS description,
Project1.id2 AS id2,
Project1.parentCategory AS parentCategory,
Project1.title AS title,
Project1.autogenerated AS autogenerated,
Project1.path AS path,
Project1.deleted AS deleted
FROM (SELECT Extent1.id AS id,
Extent1.devTitle AS devTitle,
Extent1.isAutogenerated AS isAutogenerated,
1 AS C1,
Join1.Id1 AS id1,
Join1.ItemId AS ItemId,
Join1.CategoryId AS CategoryId,
Join1.id2 AS id2,
Join1.parentCategory AS parentCategory,
Join1.title AS title,
Join1.description AS description,
Join1.autogenerated AS autogenerated,
Join1.path AS path,
Join1.deleted AS deleted,
CASE
WHEN Join1.Id1 IS NULL THEN cast(NULL as signed)
ELSE 1
END AS C2
FROM ef.items AS Extent1
LEFT OUTER JOIN (SELECT Extent2.Id AS Id1,
Extent2.ItemId AS ItemId,
Extent2.CategoryId AS CategoryId,
Extent3.id AS id2,
Extent3.parentCategory AS parentCategory,
Extent3.title AS title,
Extent3.description AS description,
Extent3.autogenerated AS autogenerated,
Extent3.path AS path,
Extent3.deleted AS deleted
FROM ef.itemcategories AS Extent2
INNER JOIN ef.categories AS Extent3
ON Extent2.CategoryId = Extent3.id) AS Join1
ON Extent1.id = Join1.ItemId
WHERE Extent1.id IN (1,2,3,4,
5,6,7,9,
10,11)) AS Project1
ORDER BY Project1.id ASC,
Project1.C2 ASC

The query is very inefficient (I event didn't have a chance to wait till it is executed). Here is on the contrary what MySQL .NET Connector generates:

explain
SELECT `Project1`.`C1`,
`Project1`.`devTitle`,
`Project1`.`id`,
`Project1`.`isAutogenerated`,
`Project1`.`C2`,
`Project1`.`CategoryId`,
`Project1`.`id1`,
`Project1`.`ItemId`,
`Project1`.`autogenerated`,
`Project1`.`deleted`,
`Project1`.`description`,
`Project1`.`id2`,
`Project1`.`parentCategory`,
`Project1`.`path`,
`Project1`.`title`
FROM (SELECT `Extent1`.`devTitle`,
`Extent1`.`id`,
`Extent1`.`isAutogenerated`,
1 AS `C1`,
`Extent2`.`CategoryId`,
`Extent2`.`Id` AS `id1`,
`Extent2`.`ItemId`,
`Extent3`.`autogenerated`,
`Extent3`.`deleted`,
`Extent3`.`description`,
`Extent3`.`id` AS `id2`,
`Extent3`.`parentCategory`,
`Extent3`.`path`,
`Extent3`.`title`,
CASE
WHEN (`Extent2`.`CategoryId` IS NULL) THEN (NULL)
ELSE (1)
END AS `C2`
FROM `items` AS `Extent1`
LEFT OUTER JOIN (`itemcategories` AS `Extent2`
INNER JOIN `categories` AS `Extent3`
ON `Extent2`.`CategoryId` = `Extent3`.`id`)
ON `Extent1`.`id` = `Extent2`.`ItemId`
WHERE (((1 = `Extent1`.`id`)
OR (2 = `Extent1`.`id`))
OR ((3 = `Extent1`.`id`)
OR (4 = `Extent1`.`id`)))
OR ((((5 = `Extent1`.`id`)
OR (6 = `Extent1`.`id`))
OR ((7 = `Extent1`.`id`)
OR (8 = `Extent1`.`id`)))
OR ((9 = `Extent1`.`id`)
OR (10 = `Extent1`.`id`)))) AS `Project1`
ORDER BY `id` ASC,
`C2` ASC

Yep, it doesn't use IN in the query... but it is much more efficient.

Idsa

Thank you, now I got it. If it was a majot feautre, I would do it. But for now I can leave tinyint and hope you will finally implement the same feature for edmx

Idsa

One more example. The following lambda:

ctx.Items.Where(i => i.DevTitle.Length == "string".Length).First();

is converted to

SELECT Extent1.devTitle AS devTitle,
Extent1.id AS id,
Extent1.isAutogenerated AS isAutogenerated
FROM ef.items AS Extent1
WHERE (Length(Extent1.devTitle)) = (Length('string'))
LIMIT 1

So Length('string') wasn't computed at query building time.

Idsa

I'm migrating my application from MySQL .NET Connectro to MySQL DotConnector. One of the motivations was that .NET Connector generates very inefficient queries. I hoped your products are much better at this as you have a lot of experience in this field. But looks like DotConnector is also not ideal.

For example, for this expression:

ctx.EnumParameterValues.Where(epv => epv.Parameter.IsAutogenerated == 0 && epv.Parameter.IsDeleted == 0);

there will be generated two joins. But if I replace it to

ctx.EnumParameterValues.Where(epv => epv.ItemId == itemId).Join(ctx.Parameters, epv => epv.ParameterId, p => p.Id, (epv, p) => new { epv, Parameter = p }).Where(at => at.Parameter.IsAutogenerated == 0 && at.Parameter.IsDeleted == 0)

I get the query with one Join as expected.

Is there any documentation or advices of which constructs are better to use? I will post some more examples in this post later.

Idsa

So if you have some additional features, I can't easily go back to edmx, right?

Idsa

Hm... I surely don't want to use not-standard model

I am migrating my application from MySQL .NET Connector and it was unpleasant to find out DotConnector doesn't map tinyint(1) to boolean as MySQL .NET Connector does.

Do you have plans to implement this feature. It is a bit strange to have "Tiny as boolean" in connection string but do not support it for the most widespread workflow.

Idsa

Looks like "Tiny As Boolean" setting doesn't work. I set it to "True" update EF model from database but still get sbyte

Idsa

So it is by design and won't be fixed, isn't it? It is very dissapointing to have no full Serializable IsolationLevel support for distributed transactions.

Are there any advantages of your approach comparing to original MySQL provider?

Idsa

AndreyR, so you tell it is not a bug but a feature, don't you?

That's why everything hangs on insert - the insert command waits for the rows to be unlocked, and they just can't be before the insert command succeeds.

I guess, it should be locked for other transactions, not for current. According to http://www.canaimasoft.com/f90sql/Onlin ... 0level.htm, "NOTE: Transaction isolation levels do not affect a transaction’s ability to see its own changes." And why does original MySQL provider work in this circumstances?

As a solution, you can either get only a subset of the table rows or use more severe isolation level (e.g., RepeatableRead).

Did you mean "less severe"?

Idsa

Could you provide any (at least rough) estimation when this bug(?) is fixed. It is very important for me as I have a choice: 1. wait till this problem is resolved 2. move to PostgreSQL. This choice will be much easier with some kind of problem resolving estimation

Devart Forums

Search found 22 matches

Converting expression trees to sql

"Tiny As Boolean" doesn't work