Select on Oracle View

PerryD · Post by **PerryD** » Thu 30 Jul 2009 18:29

I am running a linq query on a oracle view. Below is the syntax:

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 · Post by **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 · Post by **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.

AndreyR · Post by **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.

PerryD · Post by **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.