Databases
StackOverflow2013
Postsdbo
POReturn objects missing records in many to many using EF 5.0
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POReturn objects missing records in many to many using EF 5.0
    primarykey
    data
    text
    <p>Given the schema below, I'm trying to build an EF query that returns Contacts that are missing required Forms. Each Contact has a ContactType that is related to a collection of FormTypes. Every Contact is required to have at lease one Form (in ContactForm) of the FormTypes related to its ContactType.</p> <p>The query that EF generates from the linq query below works against Sql Server, but not against Oracle.</p> <pre><code>var query = ctx.Contacts.Where (c =&gt; c.ContactType.FormTypes.Select (ft =&gt; ft.FormTypeID) .Except(c =&gt; c.Forms.Select(f =&gt; f.FormTypeID)).Any()); </code></pre> <p>I'm in the process of refactoring a data layer so that all of the EF queries that work against Sql Server will also work against Oracle using Devart's dotConnect data provider. </p> <p>The error that Oracle is throwing is ORA-00904: "Extent1"."ContactID": invalid identifier. </p> <p>The problem is that Oracle apparently doesn't support referencing a table column from a query in a nested subquery of level 2 and deeper. The line that Oracle throws on is in the Except (or minus) sub query that is referencing "Extent1"."ContactID". "Extent1" is the alias for Contact that is defined at the top level of the query. <a href="http://www.devart.com/dotconnect/oracle/faq.html#q56" rel="nofollow noreferrer">Here is Devart's explanation of the Oracle limitation</a>.</p> <p>The way that I've resolved this issue for many queries is by re-writing them to move relationships between tables out of the Where() predicate into the main body of the query using SelectMany() and in some cases Join(). This tends to flatten the query being sent to the database server and minimizes or eliminates the sub queries produced by EF. <a href="https://stackoverflow.com/questions/14047129/how-to-query-a-many-to-many-for-the-absence-of-records-using-ef5">Here is a similar issue solved using a left outer join</a>.</p> <p>The column "Extent1"."ContactID" exists and the naming syntax of the query that EF and Devart produce is not the issue. </p> <p>Any ideas on how to re-write this query will be much appreciated. The objective is a query that returns Contacts missing Forms of a FormType required by the Contact's ContactType that works against Oracle and Sql Server.</p> <p><img src="https://i.stack.imgur.com/zeFYr.png" alt="enter image description here"></p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. UScResults
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload