sql query/entity framework query not returning distinct values on two tables

Question

I have a query I am running on two Tables (Table Customer and Table Vendor). On Table Customer I may have multiple rows of similar records. I am joining the two tables using the parameters below, however I noticed instances of Table Vendor Id duplicates in the query result. I am guessing this is because I have multiple similar records on Table Customer. Please can someone assist me with the right query to use to get distinct ID values on the join? Note that I am using this on entity framework core for a project. See query used on entity framework converted to sql below

SQL

select distinct * from Vendor e inner join Customer d on e.account = d.Cod_account
where (e.ActionStatus = 'Pending' and d.ActionStatus = 'Pending') and (d.Amt = e.Amount)
and (e.UploadTime = '05:00pm') and (e.Uploaddate = d.Calldate) order by d.Amt desc

ENTITY FRAMEWORK

await (from  e in _dbc.Vendor join d in _dbc.Customer on e.account equals d.Cod_account 
where (e.ActionStatus == "Pending" && d.ActionStatus == "Pending") && (d.Amt == e.Amount)
&& (e.UploadTime == uploadtime) && (e.Uploaddate == d.Calldate)
orderby d.Amt descending
      select new CustomerVend
       {
          Customer = d,
          Vendor = e
       }).Take(2000).Distinct().ToListAsync();

Thank you, that works fine. But please how do I convert it to entity framework? Do you have an idea? — blaeiz, Oct 01 '20 at 19:51
Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Oct 01 '20 at 23:03

score 0 · Answer 1 · answered Oct 02 '20 at 11:32

I've corrected your LINQ query. Distinct should be placed before Take.

var query = 
   from e in _dbc.Vendor 
   join d in _dbc.Customer on e.account equals d.Cod_account 
   where e.ActionStatus == "Pending" 
      && d.ActionStatus == "Pending"
      && d.Amt == e.Amount
      && e.UploadTime == uploadtime 
      && e.Uploaddate == d.Calldate
   orderby d.Amt descending
   select new CustomerVend
   {
      Customer = d,
      Vendor = e
   }

var result = await query.Distinct().Take(2000).ToListAsync();

sql query/entity framework query not returning distinct values on two tables

1 Answers1