Linq Left Join with Lambda Count

Question

I have a Users and a Shipments table. The Shipments table has a property Received. I want to write code to get all Users where all their shipments have been received (received == true) with linq.

Users table

public Guid Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public bool NeedsFilament { get; set; }
public ICollection<Shipment> Shipments { get; set; }

Shipments table

public Guid Id { get; set; }
public int Quantity { get; set; }
public DateTime DateShipped { get; set; }
public bool Recieved { get; set; }
public Guid UserId { get; set; } 
public User User { get; set; }

What I've done so far in a SQL query that doesn't count the Received props in Shipments but just gets the distinct (it's wrong)

SELECT DISTINCT
    [FirstName]
    ,[LastName]
    ,[Username]
    ,[Address]
    ,[LatestShippedDate]
    ,[PrinterActive]
    ,[ProductionDate]
    ,[ShippedQuantity]
    ,[Email]
    ,[PhoneNumber]
    ,[NeedsFilament]
    ,[FilamentTrackingNumber]
    ,[SentFilamentDate]
    ,[LatestShippedQuantity]
FROM 
    [dbo].[Users]
LEFT JOIN 
    Shipments ON Shipments.UserId = Users.Id
WHERE 
    Shipments.Recieved = 1 
    AND Users.NeedsFilament = 1

Does this answer to your question : https://stackoverflow.com/questions/700523/linq-to-sql-left-outer-join — Mohammed Sajid, Apr 21 '20 at 20:52
Thank you @Sajid. Not really since i want to see if any shipments have been received and at the same time select everything in the Users table — Pt4r, Apr 22 '20 at 13:39

score 0 · Answer 1 · answered Apr 23 '20 at 21:29

0

Hello @Pt4r the below Linq statement will do that you want, the below line parse the users_table and compare each element of two tables and keep those elements which statement return true

var newUserTbl = users_table.Where(user=>Shipments.UserId==user.id && Shipments.Recieved).ToList();

answered Apr 23 '20 at 21:29

19panos88

11
1
6

thank you for your answer. Where did you get the "Shipments" from? Is that the shipments table? This query doesn't really join two tables – Pt4r Apr 27 '20 at 12:16
you create two lists, one with all **Users** objects and another with all **Shipments** objects( the Shipments list inside the where statement). In the list of Users, 'users_table' in my example, perform the where statement against the "Shipments" list(for each object inside the list shipment). The above statement returns a list of Users that contains all Users where all their shipments have been received. – 19panos88 Apr 28 '20 at 09:27
@Pt4r you can also take a look in this [link](https://learn.microsoft.com/en-us/dotnet/csharp/linq/perform-left-outer-joins) maybe find it helpful – 19panos88 Apr 28 '20 at 10:35

Linq Left Join with Lambda Count

1 Answers1