Entity queries using variables do not return results
Entity queries using variables do not return results
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?
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?
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)
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?
Here is my complete connection config entry (sans login info)
Code: Select all
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?
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.
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.
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)
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)
[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).
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).
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?
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?
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:
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();
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?
Our DbSet objects (tables) are generated. Do you know of an attribute-based way that we can designate FixedLength?