I have a query where I need to select distinct order numbers from a table of orders. An order number can correspond to multiple entries in the table of orders but I only want distinct orders. I.E. I only want to grab one entry for a given order number. Then I want to join that with a customers table on o.CustomerKey equals c.CustomerKey. I have tried doing this in a couple different ways and haven’t found a fast and concise way of doing it. My thought is to select distinct orders by OrderNumber then join the Customers table where conditional criteria is met then select my data I need to return.

This is one query I tried:
public async Task<List<CustomerOrder>> GetOrdersMatchingAsync(string lastName, string address, string city, string state, string zipCode) { var query = from o in Orders join c in Customers on o.CustomerKey equals c.CustomerKey where (string.IsNullOrEmpty(lastName) || c.LastName == lastName) && (string.IsNullOrEmpty(address) || c.Address == address) && (string.IsNullOrEmpty(zipCode) || c.ZipCode == zipCode) && (string.IsNullOrEmpty(city) || c.City == city) && (string.IsNullOrEmpty(state) || c.State == state) select new CustomerOrder { OrderNumber = o.OrderNumber, CustomerNo = c.CustomerNo, CustomerKey = c.CustomerKey, FirstName = c.FirstName, LastName = c.LastName, Address = c.Address, City = c.City, State = c.State, ZipCode = c.ZipCode, }; query.DistinctBy(g => g.OrderNumber); Console.WriteLine(query.ToQueryString()); return await query.AsNoTracking().ToListAsync(); }

I have tried a couple different variations but they either have the same result, run for 30 minutes or return duplicate order numbers.