Select on Oracle View

Select on Oracle View

Postby PerryD » Thu 30 Jul 2009 18:29

I am running a linq query on a oracle view. Below is the syntax:
Code: Select all
var owner = (from view in ctx.WRD_METHOD_OWNERS_VW
                      where view.WRKEY == wrKey
                      select view).ToList();


The sql generated by this query is below:
Code: Select all
SELECT
"Extent1".BOCA_CD AS BOCA_CD,
"Extent1".BOCA_DESCR AS BOCA_DESCR,
"Extent1".ENF_PRTY_DT AS ENF_PRTY_DT,
"Extent1".FST_NM AS FST_NM,
"Extent1".LST_NM_OR_BUSN_NM AS LST_NM_OR_BUSN_NM,
"Extent1".MID_INT AS MID_INT,
"Extent1".SUFX AS SUFX,
"Extent1".WRKEY AS WRKEY
FROM (SELECT
      WRD_METHOD_OWNERS_VW.BOCA_CD AS BOCA_CD,
      WRD_METHOD_OWNERS_VW.BOCA_DESCR AS BOCA_DESCR,
      WRD_METHOD_OWNERS_VW.ENF_PRTY_DT AS ENF_PRTY_DT,
      WRD_METHOD_OWNERS_VW.FST_NM AS FST_NM,
      WRD_METHOD_OWNERS_VW.LST_NM_OR_BUSN_NM AS LST_NM_OR_BUSN_NM,
      WRD_METHOD_OWNERS_VW.MID_INT AS MID_INT,
      WRD_METHOD_OWNERS_VW.SUFX AS SUFX,
      WRD_METHOD_OWNERS_VW.WRKEY AS WRKEY
      FROM WRD.WRD_METHOD_OWNERS_VW WRD_METHOD_OWNERS_VW) "Extent1"
WHERE "Extent1".WRKEY = :p__linq__3


If I use the ADO.Net adapter and send the generated sql I get two distinct records as expected. However, if I use the Entity Framework and the linq query above, I get two identical records. The first record in the list is correct. The second record should be different but is a duplicate of the first.

Now, if I use the following linq statement:
Code: Select all
var list = (from view in ctx.WRD_METHOD_OWNERS_VW
                     where view.LST_NM_OR_BUSN_NM == last && view.BOCA_CD == basinCode
                     select new { view.WRKEY, view.LST_NM_OR_BUSN_NM, view.FST_NM }).ToList();

I also get back two distinct records as I would expect.

Why doesn't a "select *" work correctly?
PerryD
 
Posts: 10
Joined: Thu 23 Jul 2009 19:20

Postby PerryD » Mon 03 Aug 2009 22:47

Can someone even give me a hint at this? Can't replicate it? Do you need more info? Throw me a bone. Anything.
PerryD
 
Posts: 10
Joined: Thu 23 Jul 2009 19:20

Postby PerryD » Mon 03 Aug 2009 22:52

and my current workaround is to do a select on every column in the table, loop through the result set and put all the data into poco's before returning.
PerryD
 
Posts: 10
Joined: Thu 23 Jul 2009 19:20

Postby AndreyR » Tue 04 Aug 2009 07:33

You can try calling Distinct() on the first LINQ query.
I cannot reproduce the problem. If my suggestion doesn't help please send me (support * devart * com,
subject "EF Distinct") the scripts of the view and table(s) it is built over and some sample data.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby PerryD » Tue 04 Aug 2009 13:41

Thanks for the reply. I tried distinct already without luck. Don't have access to the database other than read access on the view so I will see if the customer can get me it to me.
PerryD
 
Posts: 10
Joined: Thu 23 Jul 2009 19:20


Return to dotConnect for Oracle