Databases
StackOverflow2013
Postsdbo
POhow to query a many to many for the absence of records using EF5
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POhow to query a many to many for the absence of records using EF5
    primarykey
    data
    text
    <p>Assuming a pure many to many where the primary key is the composite of two foreign keys. For example "Employee Privileges" in Northwind has EmployeeID and PrivilegeID. This m2m obviously relates the Employees table to the Privilege table.</p> <p>I need to write a query with EF that returns all Employees that are NOT associated with a specified PrivilegeID, regardless of whether or not it is related to other PrivilegeIDs. </p> <p>I have an EF query that works for Sql Server using <code>Except()</code> but it doesn't work for Oracle. </p> <p>If I were writing the query in t-sql, I would write a query that filters Employee Privileges with the required Privileges "see EmpPrivQuery below". I would then RIGHT join EmpPrivQuery to Employee as seen in EmployeesMissingPrivileges to complete the filter.</p> <p>We currently have Employee and Privilege models with relationship properties to create the many to many table. The project uses EF code first to create the schema.</p> <p>Can this be done with EF join syntax? Can it be done without creating an Entity model for the EmployeePrivilege table?</p> <pre><code>EmpPrivQuery: SELECT [Employee Privileges].[Employee ID], [Employee Privileges].[Privilege ID] FROM [Privileges] INNER JOIN [Employee Privileges] ON Privileges.[Privilege ID] = [Employee Privileges].[Privilege ID] WHERE (((Privileges.[Privilege Name])="P3")); EmployeesMissingPrivileges: SELECT EmpPrivQuery.[Employee ID], Employees.* FROM Employees LEFT JOIN EmpPrivQuery ON Employees.ID = EmpPrivQuery.[Employee ID] WHERE (((EmpPrivQuery.[Employee ID]) Is Null)); </code></pre> <blockquote> <blockquote> <p><em>This block was in original post but clouds the question. Retained for comment context.</em> I'm using Devart dotConnect as as the Oracle data provider. The error that Oracle is throwing is <code>ORA-00904: "Extent1"."EmployeeID": invalid identifier</code>. In adapting the code base that was written for sql server to work against oracle, this is a common error that I've needed to resolve. In most cases I have been able to resolve it by re-writing the query to move relationships to other tables out of the WHERE predicate (where it is very easy to be dynamic) up into the main body of the query using SelectMany(). This tends to flatten the query being sent to the database server and Oracle appears to require that.</p> </blockquote> </blockquote>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. UScResults
    1. UScResults
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COThe error indicates that the column you are querying `"EmployeeID"` does not exist. It could easily be because you have it in quotes; by default all object names are uppercase and Oracle explicitly discourages casing them. Have you tried using just `employeeid` instead (if your column name isn't cased?)
      singulars
      1. POhow to query a many to many for the absence of records using EF5
      1. USBen
    2. COWell, show the queries that you tried. Much clearer than your cloudy description. Do you mean that a `!Any()` construct did not do the job?
      singulars
      1. POhow to query a many to many for the absence of records using EF5
      1. USGert Arnold
    3. COThank you for your replies. Case isn't an issue and the column does exist. EF & Devart dotConnect are taking care of quotes and syntax.
      singulars
      1. POhow to query a many to many for the absence of records using EF5
      1. UScResults
 

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