Page 1 of 1

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

Posted: Fri 10 Aug 2018 10:24
by domrin
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

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

Posted: Mon 13 Aug 2018 09:19
by Shalex
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?

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

Posted: Mon 13 Aug 2018 14:50
by domrin
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.