Devart Forums

object

Found the problem. LINQ requires different syntax for LEFT OUTER JOIN:

var query =
from c in ctx.Channels
join cp in ctx.Channels on c.ParentChannelId equals cp.ChannelId into _cp from cp in _cp.DefaultIfEmpty()
select new {c,cp};

This one worked flawlessly.

object

Hello,

I have a table CHANNELS that can be self-joined (some channels have parent channels), so the following statement retrieves joined information on both channels and its parents:

Code: Select all

SELECT *
FROM  CHANNELS c1
LEFT OUTER JOIN CHANNELS c2 ON c1.PARENT_CHANNEL_ID = c2.CHANNEL_ID

I am using the following LINQ statement in C#:

Code: Select all

    var query =
        from c in ctx.Channels
        join cp in ctx.Channels.DefaultIfEmpty() on c.ParentChannelId equals cp.ChannelId
        select new {c,cp};

The statement generated by DotConnect looks like this:

Code: Select all

SELECT *
FROM  CHANNELS "Extent1"
INNER JOIN  (SELECT 
	"SingleRowTable1".X,
	"Extent2".CHANNEL_ID,
	"Extent2".CHANNEL_NAME,
	"Extent2".PARENT_CHANNEL_ID
	FROM   ( SELECT 1 AS X FROM DUAL) "SingleRowTable1"
	LEFT OUTER JOIN CHANNELS "Extent2" ON 1 = 1 ) "Join1" ON ("Extent1".PARENT_CHANNEL_ID = "Join1".CHANNEL_ID) OR (("Extent1".PARENT_CHANNEL_ID IS NULL) AND ("Join1".CHANNEL_ID IS NULL))

... and when executed only works like INNER JOIN, i.e. no rows with NULL PARENT_CHANNEL_ID are included in the result which of course incorrect.

We are using Oracle 11 and the DotConnect version is 9.6.570.

Is this a know issue or is there other way to express LEFT OUTER JOIN?

object

Great explanation! Thanks a lot.

object

Yes, that fixed it, thanks for a quick response! But I can't understand why without this flag it still works for some characters (including even some Sami characters) but fails for others, while having Unicode=true in the connection strings fixes it for all.

object

Hi,

We are using dotConnect for Oracle, version 8.4.333. Our Oracla database contains some data with Northern Sami national characters (columns are defined as NVARCHAR2). Here's the example of the text: "Bearráigeahččojođiheaddji".

If we try to lookup up a record with such text using dotConnect (with Entity Framework or directly, it doesn't matter), the search returns no results. I tested the application with Entity Framework Profiler, and the statement is generated correctly:

Code: Select all

SELECT "top".ROLE_ID,
       "top".ROLE,
       "top".PURPOSE,
       "top".REFERENCE,
       "top".LINK,
       "top".LABEL,
       "top".RESTRICTION,
       "top".CREATED,
       "top".CHANGED
FROM   (SELECT "Extent1".ROLE_ID,
               "Extent1".ROLE,
               "Extent1".PURPOSE,
               "Extent1".REFERENCE,
               "Extent1".LINK,
               "Extent1".LABEL,
               "Extent1".RESTRICTION,
               "Extent1".CREATED,
               "Extent1".CHANGED
        FROM   ROLES "Extent1"
        WHERE  (LOWER("Extent1".ROLE)) = N'bearráigeahččojođiheaddji'
        ORDER  BY "Extent1".ROLE_ID ASC) "top"
WHERE  ROWNUM <= 1

If I execute this statement using Oracle client tools, I get the correct result, so apparently both statement generation and statement execution by Oracle works correct. But if I execute it from C# code using Devart.Data.Oracle.dll, no results are returned. What can go wrong?

object

Thanks, I checked both OrmLite and Simple.Data, and while the first one seemed to be hard wired to Oracle client, Simple.Data Oracle adapter could be adjusted to use dotConnect provider instead of Oracle (although I had to recompile it from source to achieve this).

Best regards
Vagif

object

Hello,

We are using dotConnect Entity Framework provider for Oracle, and now in addition to that we need to manage a small database where use of Entity Framework is an overkill. What we have in mind is to use one of so called microORMs, such Simple.Data or ServiceStack OrmLite. I tried to compile a small test with OrmLite, but the fails with the following message:

System.Exception : System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.

This is understandable since I don't have Oracle client installed on my machine and would like to avoid that (we have to manage several machines, so not fiddling with Oracle clients on each of them gives us great advantage, and dotConnect has been good in releasing us from it). My connection code looks like this:

var connectionString = ConfigurationManager.ConnectionStrings["OracleConnection"];
var dbFactory = new OrmLiteConnectionFactory(connectionString.ConnectionString, OracleDialect.Provider);

And here's a connection string:
<connectionStrings>
<add name="OracleConnection" connectionString="User Id=XXX;Password=XXX;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=XXX)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XXX)));Persist Security Info=True" providerName="Devart.Data.Oracle"/>
</connectionStrings>

So the question is whether it's possible at all to replace Oracle client with dotConnect with such ORMs. Has anyone succeeded or there is no way to use these microORMs with dotConnect for Oracle?

Thanks in advance.

object

Hello Alex,

Thank you for the reply. It appeared that the issue was caused by the missing Devart subfolder in Program Files MSBuild folder. Not sure why this happenned in first place, but re-installing dotConnect restored necessary Devart-specific MSBuild folders and files, and the problem was gone.

Best regards
Vagif

object

Hello,

We have an assembly that contains an Entity Framework model. The model (edml) file has standard properties:

Build action: DevartEntityDeploy
Custom tool: DevartEfGenerator

Everything works fine on local development machine and the machine with non-TFS (Jenkins) build. However, we have to move our build environment to TFS server (with TFS 2012), and there our tests fail with the exception: "MetadataException: Unable to load the specified metadata resource".

The connection string is fine, but when I checked the output folders, I found that the resulting DLL file was much smaller than it should be, it simply lacked EF model. I see that on the local machine I can find the following files in the obj folder:

DataModel.csdl
DataModel.msl
DataModel.ssdl

However these files don't exist in obj folder on the TFS build server. But build process does not fail and the resulting DLL is generated but it lacks the model.

Again, this happens only on a TFS server (with dotConnect for Oracle 3.7 installed).

Do you have an idea what it can be?

Thanks in advance

object

RajeevDebnath wrote:Records are unable to retrive because of huge size of database(10 Million) when we run the same query on 2 Million database then retrival of records has no issue. There is no exception, query takes long time then timeout.

View contains all the standard data types (string, Decimal, datetime and Binary)

1. While creating .edmx file we not selected direct option checkbox, hope it will look for OCI connection mode.

2. No there are no user defined datatypes

3. 10.2.0.4.

Please suggest inorder to retrive records from huge database.

Regards,
Rajeev

Hi Rajeev,

Just an observation: 10 million rows is not a huge number. Both Oracle and dotConnect is used in environments with much bigger number of records per table. So there is absolutely no reason why you should have problems with 10 millon or 100 million rows. But as others pointed out, 200 columns sounds like a possible design problem. Can you think through the solution, do you really need so many at once? Can you split into smaller, more granular data sets? Try to split your model and play with results, this will let you narrow down the problem.

Good luck.

object

Thanks!

object

Hi,

I wonder about the status of this issue. Is it reproduced? Do you need help reproducing it?

object

We had to rollback latest dotConnect version because of a failure it caused to Oracle stored procedures.

Here is a stored procedure definition in a model:

Code: Select all

"DistributionFiles" is a fake entity set, i.e. there is no such table in Oracle database, it is defined to server as a complex type (because complex types are not supported by OData that we're using).

This definition used to work with previous versions of dotConnect. However latest release causes the following exception:

Code: Select all

Test 'Nrk.OnDemand.Backend.GranittModel.IntegrationTests.GranittModelTests.get_files_for_upload_should_return_collection' failed:
	Test method Nrk.OnDemand.Backend.GranittModel.IntegrationTests.GranittModelTests.get_files_for_upload_should_return_collection threw exception: 
System.Data.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.InvalidCastException: Unable to cast object of type 'Devart.Data.Oracle.OracleParameter' to type 'Devart.Data.Oracle.Entity.m'.
	at Devart.Data.Oracle.Entity.f.d()
	at Devart.Data.Oracle.Entity.f.b(CommandBehavior A_0)
	at HibernatingRhinos.Profiler.Appender.ProfiledDataAccess.ProfiledCommand.ExecuteDbDataReader(CommandBehavior behavior)
	at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
	 --- End of inner exception stack trace ---
	at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
	at System.Data.Objects.ObjectContext.CreateFunctionObjectResult[TElement](EntityCommand entityCommand, EntitySet entitySet, EdmType edmType, MergeOption mergeOption)
	at System.Data.Objects.ObjectContext.ExecuteFunction[TElement](String functionName, MergeOption mergeOption, ObjectParameter[] parameters)
	at System.Data.Objects.ObjectContext.ExecuteFunction[TElement](String functionName, ObjectParameter[] parameters)

Any help is appreciated.

object

Great news! Thanks for the fix.

P.S. Any chance to have a look at FunctionImport bug that I posted few days ago?

Oops. Didn't see your reply to that one. You've reproduced that too! Awaiting for the fix

object

Hi,

Starting apprx. from previous version, I have to manually edit models generated with dotConnect for Oracle if they have FunctionImport section.

There is an EntitySet DistributionFiles, and here is the definition that works:

Here's the one that causes problems (error from custom tool run):

I.e. inclusion of "EntitySet" attribute causes the error, and dotConnect includes it.

NB! Actually the situation is even more complicated. I first need to remove "EntitySet" attribute to be able to run custom tool, and then once the C# entity file is generated I need to bring FunctionImport EntitySet attitbute back to the model file.

Devart Forums

Search found 91 matches

Re: Incorrect LEFT OUTER JOIN generated for DefaultIfEmpty

Incorrect LEFT OUTER JOIN generated for DefaultIfEmpty

Re: Lookup for text with sami characters returns no results

Re: Lookup for text with sami characters returns no results

Lookup for text with sami characters returns no results

Re: Can dotConnect be used with ormLite or Simple.Data without installing Oracle client?

Can dotConnect be used with ormLite or Simple.Data without installing Oracle client?

Re: EF model is not embedded when running TFS Team Build

EF model is not embedded when running TFS Team Build

Stored procedure problem in dotConnect 6.60.268

Error generating model file with FunctionImport