Are You Accidentally Crippling Your EF Core Queries?

Поделиться
HTML-код
  • Опубликовано: 23 ноя 2024

Комментарии • 114

  • @MilanJovanovicTech
    @MilanJovanovicTech  2 месяца назад +1

    Do you want to simplify your development process? Grab my free Clean Architecture template here: bit.ly/3Andaly
    Want to master Clean Architecture? Go here: bit.ly/3PupkOJ
    Want to unlock Modular Monoliths? Go here: bit.ly/3SXlzSt

    • @sarathkruthiventy4872
      @sarathkruthiventy4872 2 месяца назад

      Hi Milan! When ef core did Cartesian product without split query. .did that minimal api end point also returns 68k rows for that emp id similar to SQL results?

  • @eugene5096
    @eugene5096 2 месяца назад +23

    100K RUclipsr , congrats brother !!!

  • @mohammadjaber3898
    @mohammadjaber3898 2 месяца назад +9

    This is exactly why i allows have the sql profiler open
    My problem with the new generation of developers whos working EF without having any sql knowledge or how EF is translating
    I saw over a 20 include in 1 EF core call

    • @MilanJovanovicTech
      @MilanJovanovicTech  2 месяца назад +6

      This is why I always say to learn SQL first 😁

    • @pilotboba
      @pilotboba 2 месяца назад

      You would also have people that don't realize the SQL query that EF writes is bad also. Cartesian explosion was an issue well before people started using ORMs. :)

  • @dusanknezevic9072
    @dusanknezevic9072 2 месяца назад +2

    Ah, the cartesian explosion.
    SQL is one of most underrated skills in my opinion. Great SQL skills (and DB skills in general, not just SQL) usually make your software 20x better, wheter you use ORM or not

    • @MilanJovanovicTech
      @MilanJovanovicTech  2 месяца назад

      Hey Dule :) This is an interesting one, since you'd get a similar problem even with SQL. I did exaggerate the issue by using a very large dataset, but it's still interesting.

  • @gigantedocil
    @gigantedocil 2 месяца назад +13

    Another way to get around this issue without having to use split queries if you're using Postgres is by using the built-in json aggregate functions. Instead of the database returning tons of duplicated rows due to the cartesian explosion problem you explained, it returns the exact JSON that you need. Not sure how this feature works with EF Core, but it is indeed really useful and it would be cool if you could compare both approaches!

    • @christian.mar.garcia
      @christian.mar.garcia 2 месяца назад

      Returning json from sql is also possible in SQL server since the 2016 version. You have to store the result in a varchar(max) variable and return it.

    • @gigantedocil
      @gigantedocil 2 месяца назад

      @@christian.mar.garcia Nice, didn't know. But how do you create the structure of the JSON? Seems like you would be returning a stringified version of the result set?

    • @christian.mar.garcia
      @christian.mar.garcia 2 месяца назад +2

      @@gigantedocil there are some json functions in t-sql for getting the query results in json format. You have to decide if you want to include an empty array for no data or null for some related data not present.

    • @MilanJovanovicTech
      @MilanJovanovicTech  2 месяца назад +3

      Hopefully we get a batching API in EF Core one day

    • @jordyvu999
      @jordyvu999 2 месяца назад +1

      don’t depend on EF or any orm for complex join queries with perf critical, you don’t know what happened when running, in this case call ef with your raw sql that you can control it.

  • @antonmartyniuk
    @antonmartyniuk 2 месяца назад +2

    Congratulations on 100K subscribers

  • @DaminGamerMC
    @DaminGamerMC 2 месяца назад

    The thing is sometimes, for more complex use cases, you need joins and left jokns and many queries working together

  • @victorcomposes
    @victorcomposes 2 месяца назад +1

    Congratulations on 100K.

  • @juanpaoc1728
    @juanpaoc1728 2 месяца назад +4

    The cross-product in 2:54 is wrong, isn't it? It's not combining the rows correctly right? Correct me if I'm wrong

    • @MilanJovanovicTech
      @MilanJovanovicTech  2 месяца назад +4

      Yep, didn't update the yellow table. But seems I fixed it in the 3x3 example

    • @LucasRosinelli
      @LucasRosinelli 2 месяца назад +2

      I was going to leave a similar comment 😅 thanks @MilanJovanovicTech keep the good work

  • @javidibra6786
    @javidibra6786 2 месяца назад

    amazing content man , thanks

  • @sandrorevazishvili1113
    @sandrorevazishvili1113 Месяц назад

    Putting includes before .Where() won't make query slower?

    • @MilanJovanovicTech
      @MilanJovanovicTech  Месяц назад

      It won't matter, since it's all translated into one SQL query

  • @xopabyteh
    @xopabyteh 2 месяца назад

    Amazing video, thanks

  • @shubhammondal9048
    @shubhammondal9048 2 месяца назад +1

    Congratulations for 100k.

  • @orterves
    @orterves 2 месяца назад +4

    ORMs make this such an easy thing to get wrong by mistake, though even in the raw sql it's not necessarily obvious at a glance. It's a shame that returning complex objects from relational SQL databases never really became a 1st class feature, and we still have to resort to hacks like embedding into json or (ugh) XML, or otherwise fiddle with multi-result sets.

    • @MilanJovanovicTech
      @MilanJovanovicTech  2 месяца назад +3

      I don't think the ORM is to blame here

    • @FrancoGasperino
      @FrancoGasperino Месяц назад

      Problem is objects, not SQL. Data is first class.

    • @orterves
      @orterves Месяц назад

      @@FrancoGasperino I mean support for sum types as well as product types

  • @satriansyahw1725
    @satriansyahw1725 2 месяца назад

    It’s more like in sql , u create temporary table 1 and 2 and then combine them before return to client

  • @investycoon-app
    @investycoon-app 2 месяца назад

    It's a little bit off topic. Which IDE to use for doing .NET on MacOS ? Since few months, Visual Studio is deprecated.
    Thanks for all your videos !

  • @AhmedMohammed23
    @AhmedMohammed23 4 дня назад

    would as no tracking with identity help here?

  • @alexandrosKo
    @alexandrosKo 2 месяца назад

    According to EF Core documentation:
    “Note that cartesian explosion does not occur when the two JOINs aren't at the same level”
    Are you sure your example applies to cartesian explosion case?

  • @scottvaughan
    @scottvaughan 2 месяца назад +1

    How did you setup your benchmark project to use the AppDbContext?

    • @MilanJovanovicTech
      @MilanJovanovicTech  2 месяца назад

      Just hardcoded the connection string

    • @scottvaughan
      @scottvaughan 2 месяца назад

      @@MilanJovanovicTech any chance of the source code. Would like to try this benchmarking out for myself.

  • @PetrVejchoda
    @PetrVejchoda 2 месяца назад

    Is there any reason to have the Where method after include? I instinctively write the filter first as if in any other linq. I guess since it is translated into SQL anyway, it doesn't make much difference, but if it were some operations on IEnumerable, you would actually do Include on everything, before discarding majority of it.

  • @gauravkumbhare3628
    @gauravkumbhare3628 Месяц назад

    Better to use linq for complex queries

  • @Maxim.Shiryaev
    @Maxim.Shiryaev 2 месяца назад

    As for consistency, a REPEATABLE READ transaction is to the rescue. With its own implications, of course.

  • @KREKER8331
    @KREKER8331 2 месяца назад

    Great video , thank you.

  • @alexkal820
    @alexkal820 2 месяца назад

    So if i am correct you are basically saying that if you are using a lot of includes its bad. Then you are suggesting query splitting and then based on benchmarks query splitting is making it slower. Is this correct? If this is correct what is the punchline of the video? Because it is confusing.

    • @MilanJovanovicTech
      @MilanJovanovicTech  2 месяца назад +1

      I don't think you quite got this right.
      - The problem isn't _just_ the includes. The problem is JOINs on the same level, causing a Cartesian explosion.
      - Based on the benchmarks, query splitting is 9x faster in the case of a Cartesian explosion. So yes, it's needed here.
      - The other example, where we just have many JOINs but no Cartesian explosion, there isn't a benefit from split queries.

  • @lambo1707
    @lambo1707 2 месяца назад

    how did you load the related data?, did you install a package and configure something in a file?

    • @MilanJovanovicTech
      @MilanJovanovicTech  2 месяца назад

      No, just EF includes

    • @lambo1707
      @lambo1707 2 месяца назад

      @@MilanJovanovicTech interesting when we are loading related data it produces error until we configure it in program.cs

  • @casperhansen826
    @casperhansen826 2 месяца назад

    Now add a text field with Task description

  • @rohanjaykar8652
    @rohanjaykar8652 2 месяца назад +1

    could you please explian projection ? 11:00

  • @backendsamurai
    @backendsamurai 2 месяца назад

    Milan, please submit your implementation of Result pattern to Github if its possible. Thanks for your rich content 👍

  • @artemvolsh387
    @artemvolsh387 2 месяца назад +2

    2:05, please add a white screen warning

  • @zahidcakici7968
    @zahidcakici7968 Месяц назад

    Bro why you just didn't filter employeeId (Where(e => e.Id == id)) before the include ?

    • @MilanJovanovicTech
      @MilanJovanovicTech  Месяц назад +1

      What difference would it make? 😅 This is LINQ -> SQL

    • @zahidcakici7968
      @zahidcakici7968 Месяц назад

      @@MilanJovanovicTech So you are saying that EfCore optimises query and filter the where query before join or it just doesn’t matter if we filter before join or not?

  • @onaspnet
    @onaspnet 2 месяца назад

    There is a lot of magic (black box + documentation) to read about EF. This is why I like to use my own SQL with Dapper

    • @MilanJovanovicTech
      @MilanJovanovicTech  2 месяца назад

      You'd have the same problem in SQL

    • @krccmsitp2884
      @krccmsitp2884 2 месяца назад

      The EF Core docs are very comprehensive and detailed.

    • @onaspnet
      @onaspnet 2 месяца назад

      @MilanJovanovicTech however, inefficient joins are easier to observe and fix.

    • @onaspnet
      @onaspnet 2 месяца назад

      @krccmsitp2884 true and long

    • @kristiadhy
      @kristiadhy 2 месяца назад

      Do you write the SQL statement as part of the code in Dapper or do you use StoreProc? Because in my experience I have made a lot of mistakes caused by typing the wrong SQL statement. That's what I don't like about Dapper. I always prefer using strong type.

  • @TriNguyen-iz9ft
    @TriNguyen-iz9ft 2 месяца назад

    How to see the query content in EF ?

  • @omarjamil9449
    @omarjamil9449 2 месяца назад

    How can I appear database icon next to entity class? 😅😅

  • @Andrii.S16
    @Andrii.S16 2 месяца назад

    Could you suggest a tool for working with SQL Server databases? I find Azure Data Studio a bit lacking.

  • @Kirides
    @Kirides 2 месяца назад +2

    could've mentioned the ginormous difference of data (400KB vs 18MB) on the closing "Employees" benchmark
    Querysplitting can drastically improve "scalability" (as in reducing shared bandwith usage) and reduce egress costs.
    especially for common "crud" endpoints where some entities literally join 8 tables, with 4 of them being one-to-many or many-to-many.

    • @MilanJovanovicTech
      @MilanJovanovicTech  2 месяца назад +1

      I was implying that by the # of records retrieved in either example

  • @mridulpaul1378
    @mridulpaul1378 2 месяца назад

    May I get source code of it?

  • @enricoroselino7557
    @enricoroselino7557 2 месяца назад

    wait i might done this 😂 ill check asap

  • @mzuvin
    @mzuvin 2 месяца назад

    I know that pagination does not work in split query.

    • @MilanJovanovicTech
      @MilanJovanovicTech  2 месяца назад

      Probably (but that won't suffer from this issue as much since we're limiting the # of rows we return)

  • @mzuvin
    @mzuvin 2 месяца назад

    splitting the query is not a magic feature, it is proof that ef core is slow.

  • @RayZde
    @RayZde Месяц назад

    If you want performance, its simple. Dont use EF.

  • @nonadequateuser7401
    @nonadequateuser7401 2 месяца назад +2

    Am I only one guy who hates EF and don't understand why people still using it?

    • @MilanJovanovicTech
      @MilanJovanovicTech  2 месяца назад +4

      Among the few

    • @ishitvyas
      @ishitvyas 2 месяца назад +8

      Yes you are

    • @filipnikolic2280
      @filipnikolic2280 2 месяца назад +3

      Probably

    • @algorithm-artisan
      @algorithm-artisan 2 месяца назад +3

      Yes

    • @kristiadhy
      @kristiadhy 2 месяца назад +1

      So what is your alternative? I know there is another option like Dapper. But I don't like writing the SQL statement as part of the code because it's prone to errors or typos.

  • @taiphanvan2403
    @taiphanvan2403 2 месяца назад

    Is AsSplitQuery effective when I use the navigation properties in the Select method rather than Include?
    In the Select method, I only fetch some necessary properties of navigation property rather than fetch all them.
    Example:
    var employees = await context
    .Employees
    .Select(e => new EmployeeDto() {
    Salary = e.SalaryPayment...
    }).ToListAsync();

    • @xtazyxxx3487
      @xtazyxxx3487 2 месяца назад

      Try linqpad to see the sql generated by the linq query. Normally include create joins whereas projection creates subqueries

    • @MilanJovanovicTech
      @MilanJovanovicTech  2 месяца назад

      Well it's hard to tell from your small example, but you can easily test if split queries work better