LINQ Group by distinct count support

[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

[Zero-G.]

Hey

This is not a 1:1 answer, but I have code which works

Code: Select all

var query = from psc in prodDtcx.ProductSubcategory
            join p in prodDtcx.Product on psc.ProductSubcategoryID equals p.ProductSubcategoryID
            group psc by new {psc.Name, p.Color} into g
            select new { Color = g.Key.Color, ProductSubcategoryName = g.Key.Name};

maybe this helps you

[AndreyR]

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

[edvaldig]

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.

[AndreyR]

Could you please send me the script of the objects you are using in the query?
I am not able to reproduce the situation.