Devart Forums

edvaldig

Confirmed, setting the AutoGeneratedValue property in the entity editor solved my issue which was nearly identical

edvaldig

AndreyR wrote:Try the following query:
Code: Select all
var q = 
(from e in _context.Envs 
where e.State == "VER" && e.Type == "SWAP" && e.Cid == custID 
join a in _context.Assets on e.Tradeid equals a.Tradeid into ae 
select new { e.Tradeid, Ccy = ae.Select(n => n.Ccy).Distinct().Count() }).Where(temp =>temp.Ccy == 1);
Query built using the same principle succeeded using the latest 5.60.102 Beta build of dotConnect for Oracle

You must´ve misunderstood my question. Your query is exactly the same as mine except for the last .Where line which I was not focusing on because it is irrelevant to the error since the query doesn't get this far. I tried installing your latest version 5.60.102 but it is still the same. the error I get is the following:

Error on executing DbCommand
InnerException = {"ORA-00904: "T1"."TRADEID": invalid identifier"}

NOTE: This executes fine, however if you try using the results or debug through it and expand the "Results View" you will get the error.

edvaldig

Original SQL

Code: Select all

select e.tradeid
from envs e, assets a
where e.state = 'VER' 
   and e.atype = 'SWAP' 
   and e.cid = '12312412515'
   and e.tradeid = a.tradeid
group by e.tradeid
having count(distinct a.ccy) = 1

Updated SQL to convert to LINQ

Code: Select all

select e.tradeid, count(distinct a.ccy) as ccy
from envs e, assets a
where e.state = 'VER' 
   and e.atype = 'SWAP' 
   and e.cid = '12312412515'
   and e.tradeid = a.tradeid
group by e.tradeid

I've been trying all sorts of ways to translate this and get it to work with devart LINQ to SQL. It always fails on the distinct operator, saying:

ORA-00904: T1.TRADEID: invalid identifier

Here is an example LINQ that fails and gives the above error (works if I skip the .Distinct() part):

Code: Select all

.. = from e in _context.Envs
where e.State == "VER" && e.Type == "SWAP" && e.Cid == custID
join a in _context.Assets on e.Tradeid equals a.Tradeid into ae
select new { e.Tradeid, Ccy = ae.Select(n => n.Ccy).Distinct().Count() };

I've tested it with a group statement, but with similar results.

I read somewhere that you need to create your own IEqualityComparer for the Distinct method to work but I think it's an unacceptable overhead to create one for each and every query including the distinct call.

Note: The tables we are reading from are on two different schemas accessed with read-only permissions and will stay that way.

So I guess this comes down to three questions:

1) How would you make the original query work in LINQ (if possible)?

2) Is LINQ2SQL suitable for applications like this, with complicated sql queries? DBMonitor showed me how some of those queries look like and it seems to generate pretty bad inner queries and stuff that normally would make performance suffer. Also, the readability of the LINQ query code is worse than the SQL version

3) There are cases where LINQ suits perfectly so we don´t want to drop it entirely just if it can´t handle the more complex queries. Is there a way for us to perform sql-string queries directly on our context? I´ve tried creating a custom method in the entityframework and it didn't work since it wasn´t possible to make the function return a table or a list of values.

Thanks

edvaldig

Excellent, there it is, thank you! For future reference:

edvaldig

Hi,

Background:
-Version: Visual Studio 2008
-Database: Oracle
-Product: Devart OraDeveloper Tools 2.55 for Visual Studio

I am trying to use the entity developer to create a model for a read only oracle schema. My problem is that the tables I need to access are not under the schema matching the login name, e.g. User: USER@DB needs to access tables at [email protected]_TABLE.
Now you might tell me to do stuff so these tables are included in the USER schema, e.g. by creating views. That is not possible in my case, I have a read only login with no rights but to read from some other schemas, and it will stay that way. The Database Explorer shows no tables, since it doesn't have an option to show all available tables for that user. This option is, however, visible when creating connections via the database explorer inside Visual studio (View->Devart developer Tools->Database Explorer).

We are looking for a permanent solution for accessing Oracle databases in all application development for our department and this seems to be the right tool for us. If you are able to help me solve this within a few days, then you have your self a large customer, we need to sort this out asap.

Devart Forums

Search found 5 matches

LINQ Group by distinct count support

"Show All Objects" missing in Entity Developer!