At 2: When i create an extension method on the PostEntity and then call Select(post => post.toDto()) does this also only take the proeprties i need or does this take all properties and discards the rest again?
For the cartesian explosion issue I think the best approach is to use json functions to return the exact json that you want (e.g., in Postgres you have json_agg function). This way you only have one query to the database and not only you don't return duplicate data, you also return it in a much better format. If you're using Node.js you can immediately use the returned JSON instead of having to map it into objects in your application layer. Not sure if this is supported by .NET and Entity Framework, though.
Hey, what happened to that video you released maybe a year ago regarding a fitness startup you were working on? Was eagerly waiting episode 2 but seems episode 1 is gone now.
I think that what's described as cartesian explosion is not really a cartesian explosion. It still returns the same amount of rows, but with more data.
Yeah you’re right. It’s not exactly the Cartesian Explosion. But I wanted to keep it simple without having to explain the cross product of rows etc. Just wanted to highlight the use of SplitQuery when doing joins on children tables to reduce the data duplication. Same principle applies, just easier to explain with a single join
@@jonowilliams26 By trying to "keep it simple" youve actually misinterpreted how a database works with such joins. It is still a single query, not two queries for example, and the duplication does not actually happen, since they are separate entities on the database. The database engine will duplicate the "Author" in this case, to multiple rows, if the query asked that. To reiterate, there is no data duplication here, especially if youre returning "SingleOrDefault". Spreading incorrect information shouldnt be a means to an end. Pick a proper example to explain SplitQuery. This should be fixed in the video. Also, for showing EF related topics, it would probably be benefitial to also show the underlying queries and how they change based on the different chainings and methods.
@@TehHeiks Running the following code will produced the following output. Please note the use of TagWith() which will add a comment to SQL Query Output As you see in the output, without using AsSplitQuery it will do a join, resulting in the post information being duplicated for every comment it has. When using AsSplitQuery it generates TWO SQL Queries. Which is exactly what I said. Maybe you should be following my tips so you know what you're talking about next time 😂😂 CODE: var x = await db.Posts .AsNoTracking() .TagWith("WITHOUT Split Query") .Include(x => x.Comments) .SingleOrDefaultAsync(x => x.Id == request.Id, ct); var y = await db.Posts .AsNoTracking() .AsSplitQuery() .TagWith("As Split Query") .Include(x => x.Comments) .SingleOrDefaultAsync(x => x.Id == request.Id, ct); OUTPUT [17:12:44 INF] Executed DbCommand (7ms) [Parameters=[@__request_Id_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30'] -- WITHOUT Split Query SELECT [t].[Id], [t].[Content], [t].[CreatedAtUtc], [t].[LastUpdatedAtUtc], [t].[UserId], [c].[Id], [c].[Content], [c].[CreatedAtUtc], [c].[PostId], [c].[ReplyToCommentId], [c].[UpdatedAtUtc], [c].[UserId] FROM ( SELECT TOP(2) [p].[Id], [p].[Content], [p].[CreatedAtUtc], [p].[LastUpdatedAtUtc], [p].[UserId] FROM [Posts] AS [p] WHERE [p].[Id] = @__request_Id_0 ) AS [t] LEFT JOIN [Comments] AS [c] ON [t].[Id] = [c].[PostId] ORDER BY [t].[Id] [17:12:44 INF] Executed DbCommand (5ms) [Parameters=[@__request_Id_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30'] -- As Split Query SELECT TOP(2) [p].[Id], [p].[Content], [p].[CreatedAtUtc], [p].[LastUpdatedAtUtc], [p].[UserId] FROM [Posts] AS [p] WHERE [p].[Id] = @__request_Id_0 ORDER BY [p].[Id] [17:12:44 INF] Executed DbCommand (7ms) [Parameters=[@__request_Id_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30'] -- As Split Query SELECT [c].[Id], [c].[Content], [c].[CreatedAtUtc], [c].[PostId], [c].[ReplyToCommentId], [c].[UpdatedAtUtc], [c].[UserId], [t].[Id] FROM ( SELECT TOP(1) [p].[Id] FROM [Posts] AS [p] WHERE [p].[Id] = @__request_Id_0 ) AS [t] INNER JOIN [Comments] AS [c] ON [t].[Id] = [c].[PostId] ORDER BY [t].[Id]
I also just started with EF But my problem with EF is that when creating the tables by the models(InitialCreate), it is never how i wanted it. Or I have to go through a lot of errors before i can do the Update-Database. For example I have a match class Like: Id, MatchDateTime, Team1, Team2. A Team class (id, name, List), and Player class: Id, TeamId, Name. You first get problems with Team1 and Team2 both having a reference to Team, so you already have problems with DeleteBehavior. Then there is no way(or at least i didnt find one) to get Player working without adding a field: public FootballTeam Team { get; set; }. A field i will never use in my app...
That's too much value in a single video
Keep going bud 🎯
Thank you! Appreciate the positive feedback!
Nice video! Keep them coming
Thanks man! Have another one tomorrow
Sensei!
At 2: When i create an extension method on the PostEntity and then call Select(post => post.toDto()) does this also only take the proeprties i need or does this take all properties and discards the rest again?
This might be a dumb question, but in these examples, why not use the asynchronous way?
For the cartesian explosion issue I think the best approach is to use json functions to return the exact json that you want (e.g., in Postgres you have json_agg function). This way you only have one query to the database and not only you don't return duplicate data, you also return it in a much better format. If you're using Node.js you can immediately use the returned JSON instead of having to map it into objects in your application layer. Not sure if this is supported by .NET and Entity Framework, though.
Hey, what happened to that video you released maybe a year ago regarding a fitness startup you were working on? Was eagerly waiting episode 2 but seems episode 1 is gone now.
Decided to stop pursing that idea, found out there was something really similar so no point
Nice tips. Can i ask what vsc theme are you using? and what is the drawing board called?
I think the theme is called Night Owl, and the drawing software is Excalidraw
In the 1st one, there's also the additional benefit of not converting to an array _twice_.
I think that what's described as cartesian explosion is not really a cartesian explosion. It still returns the same amount of rows, but with more data.
Yeah you’re right. It’s not exactly the Cartesian Explosion. But I wanted to keep it simple without having to explain the cross product of rows etc. Just wanted to highlight the use of SplitQuery when doing joins on children tables to reduce the data duplication. Same principle applies, just easier to explain with a single join
@@jonowilliams26 By trying to "keep it simple" youve actually misinterpreted how a database works with such joins. It is still a single query, not two queries for example, and the duplication does not actually happen, since they are separate entities on the database. The database engine will duplicate the "Author" in this case, to multiple rows, if the query asked that. To reiterate, there is no data duplication here, especially if youre returning "SingleOrDefault". Spreading incorrect information shouldnt be a means to an end. Pick a proper example to explain SplitQuery. This should be fixed in the video.
Also, for showing EF related topics, it would probably be benefitial to also show the underlying queries and how they change based on the different chainings and methods.
@@TehHeiks Running the following code will produced the following output. Please note the use of TagWith() which will add a comment to SQL Query Output
As you see in the output, without using AsSplitQuery it will do a join, resulting in the post information being duplicated for every comment it has.
When using AsSplitQuery it generates TWO SQL Queries. Which is exactly what I said. Maybe you should be following my tips so you know what you're talking about next time 😂😂
CODE:
var x = await db.Posts
.AsNoTracking()
.TagWith("WITHOUT Split Query")
.Include(x => x.Comments)
.SingleOrDefaultAsync(x => x.Id == request.Id, ct);
var y = await db.Posts
.AsNoTracking()
.AsSplitQuery()
.TagWith("As Split Query")
.Include(x => x.Comments)
.SingleOrDefaultAsync(x => x.Id == request.Id, ct);
OUTPUT
[17:12:44 INF] Executed DbCommand (7ms) [Parameters=[@__request_Id_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
-- WITHOUT Split Query
SELECT [t].[Id], [t].[Content], [t].[CreatedAtUtc], [t].[LastUpdatedAtUtc], [t].[UserId], [c].[Id], [c].[Content], [c].[CreatedAtUtc], [c].[PostId], [c].[ReplyToCommentId], [c].[UpdatedAtUtc], [c].[UserId]
FROM (
SELECT TOP(2) [p].[Id], [p].[Content], [p].[CreatedAtUtc], [p].[LastUpdatedAtUtc], [p].[UserId]
FROM [Posts] AS [p]
WHERE [p].[Id] = @__request_Id_0
) AS [t]
LEFT JOIN [Comments] AS [c] ON [t].[Id] = [c].[PostId]
ORDER BY [t].[Id]
[17:12:44 INF] Executed DbCommand (5ms) [Parameters=[@__request_Id_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
-- As Split Query
SELECT TOP(2) [p].[Id], [p].[Content], [p].[CreatedAtUtc], [p].[LastUpdatedAtUtc], [p].[UserId]
FROM [Posts] AS [p]
WHERE [p].[Id] = @__request_Id_0
ORDER BY [p].[Id]
[17:12:44 INF] Executed DbCommand (7ms) [Parameters=[@__request_Id_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
-- As Split Query
SELECT [c].[Id], [c].[Content], [c].[CreatedAtUtc], [c].[PostId], [c].[ReplyToCommentId], [c].[UpdatedAtUtc], [c].[UserId], [t].[Id]
FROM (
SELECT TOP(1) [p].[Id]
FROM [Posts] AS [p]
WHERE [p].[Id] = @__request_Id_0
) AS [t]
INNER JOIN [Comments] AS [c] ON [t].[Id] = [c].[PostId]
ORDER BY [t].[Id]
I also just started with EF But my problem with EF is that when creating the tables by the models(InitialCreate), it is never how i wanted it.
Or I have to go through a lot of errors before i can do the Update-Database.
For example I have a match class Like: Id, MatchDateTime, Team1, Team2. A Team class (id, name, List), and Player class: Id, TeamId, Name.
You first get problems with Team1 and Team2 both having a reference to Team, so you already have problems with DeleteBehavior.
Then there is no way(or at least i didnt find one) to get Player working without adding a field: public FootballTeam Team { get; set; }. A field i will never use in my app...
try FluentConfiguration