Where(string == string) translates to DBMS_LOB.COMPARE

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
domrin
Posts: 4
Joined: Fri 10 Aug 2018 09:55

Where(string == string) translates to DBMS_LOB.COMPARE

Post by domrin » Fri 10 Aug 2018 10:24

Hi,

I'm reviewing the Oracle provider for Entity Framework Core as part of the trial period to assess suitability for our project.

I'm trying to do a simple string comparison to get a filtered result of an entity.

My dbcontext looks something like this (all properties are strings)-

Code: Select all

modelBuilder.Entity<Item>(entity =>
{
	entity.ToTable("TABLENAME");
	entity.HasKey(model => model.Id);
	entity.Property(p => model.Id).HasColumnName("ITEMID");
	entity.Property(p => model.Summary).HasColumnName("ITEMSUMMARY");
});

If I do -

Code: Select all

var item = await _dbContext.Item.Where(model => model.Summary.Equals("sometext").ToListAsync();
It generates

Code: Select all

SELECT "model".ITEMID, "model".ITEMSUMMARY FROM "TABLENAME" "model" WHERE DBMS_LOB.COMPARE("model".SUMMARY, :p__message_Id_0) = 0"
which takes a huge amount of time to query (10+ seconds), as opposed to doing just a simple WHERE "model".SUMMARY = "sometext" which takes less than 100ms.

Strangely enough, if I change the key in the dbcontext to be the Summary field instead, it runs using the faster method.

Does anyone know why I would have this behaviour? The field I'm comparing against is a VARCHAR2 and I was under the impression that it would only do DBMS_LOB.COMPARE on CLOB fields.

Thanks

Shalex
Site Admin
Posts: 8426
Joined: Thu 14 Aug 2008 12:44

Re: Where(string == string) translates to DBMS_LOB.COMPARE

Post by Shalex » Mon 13 Aug 2018 09:19

If you do not specify column type explicitly, System.String is mapped to NCLOB by default. You can change this: viewtopic.php?t=36222. Does this help?

domrin
Posts: 4
Joined: Fri 10 Aug 2018 09:55

Re: Where(string == string) translates to DBMS_LOB.COMPARE

Post by domrin » Mon 13 Aug 2018 14:50

Shalex wrote:
Mon 13 Aug 2018 09:19
If you do not specify column type explicitly, System.String is mapped to NCLOB by default. You can change this: viewtopic.php?t=36222. Does this help?
Thanks for your reply. The link you sent was of great help.

Both config.CodeFirstOptions.UseNonLobStrings or HasMaxLength() for the affected columns which worked fine.

Thanks again for your help.

Post Reply