Databases
StackOverflow2013
Postsdbo
POWhy doesn't Distinct with Entity Framework do "DISTINCT" on SQL Server?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhy doesn't Distinct with Entity Framework do "DISTINCT" on SQL Server?
    primarykey
    data
    text
    <p>I just recently switched out our repository and context from Linq-to-SQL to Entity Framework 4.1 CodeFirst.</p> <p>All my unit tests are passing at the moment, except one! And that is the one that checks if I get a distinct result from a SQL-view.</p> <pre><code>var query = worker.ObjectRepository.GetFreeObjects(); query = query.Distinct(); return query; </code></pre> <p>When I inspect the query just before I call .Distinct(), the query is correct and it runs fine when I execute it manually on SQL Server.</p> <p>But after I call <code>.Distinct()</code>, the query just looks the exact same as before.</p> <p>If I manually add a DISTINCT to my SQL query, it works fine, but calling <code>.Distinct()</code> in code doesn't do this.</p> <p>Why is that? </p> <p><strong>Edit</strong></p> <pre><code>public IQueryable&lt;SearchResultValueObject&gt; GetFreeObjects() { return Get(Context.FreeObjects) } public IQueryable&lt;TEntity&gt; Get&lt;TEntity&gt;(IQueryable&lt;TEntity&gt; query) { return query; } </code></pre> <p>I simplyfied a bit becaus at this scenario that's all that is happening. Grabbing the data from the Context, and returning it.</p> <p>This is how my Context look</p> <pre><code>public class MyContext : DbContext, IMyContext { public virtual DbSet&lt;SearchResultValueObject&gt; SearchResult { get; set; } IQueryable&lt;SearchResultValueObject&gt; IMyContext.FreeObjects { get { return this.SearchResult; } } } </code></pre> <p>I also tried to remove everything except the objectNo from the SearchResultMap-file, I got the results from the Db, but <code>.Distinct()</code> didn't work.</p> <p><strong>Edit2</strong></p> <p>I made a simple example that has the same problem: <a href="http://pastebin.com/sYVfNb0Y" rel="nofollow">http://pastebin.com/sYVfNb0Y</a> Does this has something to do with the fact that I'm fetching from a view? Or am I missing something important here?</p> <p>Best regards Jesper</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USHerrLiljegren
    1. USHerrLiljegren
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POWhy doesn't Distinct with Entity Framework do "DISTINCT" on SQL Server?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COYou'll need to show the code for worker.ObjectRepository.GetFreeObjects().
      singulars
      1. POWhy doesn't Distinct with Entity Framework do "DISTINCT" on SQL Server?
      1. USJustin Niessner
    2. COFor the linked example code @adrift's answer applies: You query for the full entity (no projection) therefore the primary key is one of the columns. In this case using `DISTINCT` or not would give the same result. Obviously EF recognizes this and omits the `DISTINCT` altogether.
      singulars
      1. POWhy doesn't Distinct with Entity Framework do "DISTINCT" on SQL Server?
      1. USSlauma
    3. COBut when I take the query EF generates, and run it in the SQL Management Studio with the added DISTINCT, it will return distinct rows. Is there any solution to get the desired effect?
      singulars
      1. POWhy doesn't Distinct with Entity Framework do "DISTINCT" on SQL Server?
      1. USHerrLiljegren
 

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