It's pronounced Diane. I do data architecture, operations, and backend development. In my spare time I maintain Massive.js, a data mapper for Node.js and PostgreSQL.
It's pronounced Diane. I do data architecture, operations, and backend development. In my spare time I maintain Massive.js, a data mapper for Node.js and PostgreSQL.
I'm focused on developing and expanding my knowledge and skills. Enjoying new challenges. I'm assuming that there are no stupid questions, there are only silly answers.
In my opinion, this is a better approach, which doesn't require an additional call to the database and doesn't require to store entities in the memory (which could have terrible results in case of a huge collection).
And of course, it is available in LINQ.
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (110ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [user].[Id], [user].[Email], [user].[CustomerId], [user].[UserName]
FROM [AspNetUsers] AS [user]
WHERE [user].[CustomerId] IS NULL
And here is the EF SQL of "Customers":
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (16ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [t].[Id], [t].[Email], [t].[FullName], [t].[RegisteredAt], [t].[CreatedAt], [t].[UpdatedAt]
FROM [Customers] AS [t]
As we can see, the IN logic really occurs in memory.
Thank Alex,
the translation looked easy to understand as the LINQ version matches with SQL statement 👍.
And I have a question regarding the conversion.
Would the LINQ version fetch all data from database first and filter (using
Where
clause) in memory?or
would the query create an optimal syntax to send to the database and fetch only filtered records?
Calling
ToList()
results in execution of the query. So the first statement will make an in memory list of email addresses.To get a better feel for what's happening you can intercept the actual queries. Assuming Entity framework is used: Log Queries executed by Entity Framework DbContext.
I stopped writing C# right as LINQ got popular so I don't know it too well but I believe this does store the list of emails in memory.
In SQL you could do this:
and achieve the same result without a subquery. I don't know how you'd go about translating that to LINQ though.
It's true! But the tables are in different contexts, which means they are in different databases. So, it's not possible to make a
JOIN
between them.My condolences :)
In my opinion, this is a better approach, which doesn't require an additional call to the database and doesn't require to store entities in the memory (which could have terrible results in case of a huge collection).
And of course, it is available in LINQ.
Here is the EF SQL of "Users":
And here is the EF SQL of "Customers":
As we can see, the
IN
logic really occursin memory
.Thanks for the clarification Alex 😎