Entity queries using variables do not return results

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
jmarsch
Posts: 5
Joined: Mon 02 May 2011 23:15

Entity queries using variables do not return results

Post by jmarsch » Mon 02 May 2011 23:27

This has to be some sort of config problem, but I don't know what's going on. If I use a variable in the "where" clause of my linq query, I get no results.

Here's an example:
this query works fine (returns exactly 1 item as expected):
(notice that I use a string literal in the "where" clause)

var rec = from u in Users
where u.Username == "AUser"
select u;

This query never returns results:
(the only difference is I use a variable in the "where")

string userName = "AUser";
var rec = from u in Users
where u.Username == userName
select u;

What's going on?


EDIT
I have another clue to the problem, but I don't know what the best solution is:
The column referenced by the where clause is a fixed-length character column. However, according to dbMonitor, the SQL sent to Oracle specifies a VarChar as the parameter type. I have found that if I pad the string with spaces, I get the results I need, but there has to be some way to avoid the need to pad. Is there some config I can apply? An attribute to my Entity Framework POCO's?

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

Post by Shalex » Tue 03 May 2011 11:36

Please try using the "Trim Fixed Char=false;" connection string parameter.

jmarsch
Posts: 5
Joined: Mon 02 May 2011 23:15

Post by jmarsch » Tue 03 May 2011 16:38

I tried, but I still have the problem. According to dbMonitor, the parameter is still passed as a VarChar. Did I format the Trim Fixed Char parameter correctly?

Here is my complete connection config entry (sans login info)

Code: Select all

    

According to the devArt ConnectionString docs here: http://www.devart.com/dotconnect/oracle ... tring.html

it looks as though Trim Fixed Char affects column data that is returned in a query. The problem that I am having is with the content of a parameter that is used in a where clause. I don't think that Trim Fixed Char covers teh same issue. Is there something else I can try?

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

Post by Shalex » Wed 04 May 2011 12:23

I cannot reproduce the problem with current (6.30.145 Beta) build of dotConnect for Oracle. Could you please upgrade to the latest version and notify us about the results?

If it doesn't help, please send us a small test project with the corresponding DDL/DML script to reproduce the issue in our environment. Also tell us your version (x.xx.xxx) of dotConnect for Oracle.

jmarsch
Posts: 5
Joined: Mon 02 May 2011 23:15

Post by jmarsch » Wed 04 May 2011 16:24

I can still repro with the beta. If you send me a private email address, I will send you the DDL for the oracle table the POCO class that we are using, and the query that I am using in LINQPad.

I have found one other solution, but it's not a good one for us:
In my POCO class, I can add the column attribute like so:
[Column("ColumnName", TypeName="CHAR")]

That fixes the problem. Problem is that this code must support SQL Server and Oracle both. Adding the TypeName causes a runtime error when I connect to SQL Server. (the TypeName parameter is defined as a Provider-specific name, so it's not surprising that an Oracle typename should break SQL Server)

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

Post by Shalex » Thu 05 May 2011 11:28

[Update]
As we understood, you are working with Entity Framework 4.1 CodeFirst. Basing on your scenario, we recommend you using fluent mapping. Here is a chain of inheritance for your task: DbContext -> MyDbContext -> OracleDbContext | SqlDbContext. Override the OnModelCreating() method in the OracleDbContext class to set the necessary fluent mapping.

An example of using OnModelCreating(): http://www.devart.com/blogs/dotconnect/ ... qlite.html , the "DbContext Creating, Mapping Customization" section.

An example of fluent mapping: we have sent you an e-mail with the "DbContext Template" article (it will be available in our blog soon). Pay attention to the "Simultaneous Use of Several Databases" section - there two alternative ways to identify Char column:

a) modelBuilder.Entity()
.Property(p => p.CharField)
.HasColumnType("CHAR");

b) modelBuilder.Entity()
.Property(p => p.CharField)
.IsFixedLength();

[/Update]

If this information doesn't help, please send us a small test project (without using LINQPad) with the corresponding DDL/DML script to reproduce the issue in our environment: http://www.devart.com/company/contact.html (include the reference to this thread in your request).

jmarsch
Posts: 5
Joined: Mon 02 May 2011 23:15

Post by jmarsch » Tue 10 May 2011 14:59

Thank you -- we have the fluent API working. One other thing -- when we were using an earlier version of Oracle Connect (don't have the version number, but it was around Jan-Feb of this year), We did not have to do this to make it work.

Our solution needs to work with both SQL Server and Oracle. We can use the fluent API to make the oracle fix-up code conditional, but if there is some other way to do this that does not require the code (some connection string option? -- it definitely worked in the older version), then that would be beneficial to us.

The support individual I have been working with had been unable to reproduce the problem. This gave us some hopes that maybe this is just a configuration issue. On Friday last week, I sent a sample program along with DDL script to create the test table. Have you been able to reproduce the behavior with it?

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Wed 11 May 2011 13:04

It should have been worked in older versions without Code First, am I right?
You should indicate that column has fixed length (or CHAR data type) in order to generate the correct parameter type.
After adding these two lines in your test project Fluent mapping everything worked correctly:

Code: Select all

modelBuilder.Entity().Property(p => p.Column1).IsFixedLength();
modelBuilder.Entity().Property(p => p.Column2).IsFixedLength();


jmarsch
Posts: 5
Joined: Mon 02 May 2011 23:15

Post by jmarsch » Wed 11 May 2011 15:44

yep -- that does it! Thank you. Last question -- in our model,we have to hand-code our dbContext objects, so it's a bit painful to have to write a fluent call for every column of every table.

Our DbSet objects (tables) are generated. Do you know of an attribute-based way that we can designate FixedLength?

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Thu 12 May 2011 13:58

Unfortunately, there is no IsFixedLength attribute available.
You cannot set the CHAR column type because of two DBMS using the same model.
So the only option available is to use Fluent mapping.

Post Reply