Alta Posts - Answer Overflow

Alta

•Created by Alta on 7/9/2024 in #help

EF Core - Where clause on multiple field in object list

Hi everybody I'm using sql through EF Core, here at work, and I have a problem querying multiple results from a table In SQL, I've got that table of Foo with an index on field a and b In my code, I've got a list of Bar containing, quite conveniently, two field a and b I'd like to do something along the line of

sqlContext.Foo.Where(f => f.a == bar.a && f.b == bar.b);

sqlContext.Foo.Where(f => f.a == bar.a && f.b == bar.b);

But where foo could match with any element of an enumerable of bar My colleague suggest something looking like this

IEnumerable<string> stringifiedBar = bars.Select(bar => $"{bar.a}{bar.b}");
sqlContext.Foo.Where(foo => stringifiedBar.Contains(foo.a+foo.b));

IEnumerable<string> stringifiedBar = bars.Select(bar => $"{bar.a}{bar.b}");
sqlContext.Foo.Where(foo => stringifiedBar.Contains(foo.a+foo.b));

Super, that works But... It's slow as heck Our guess is that it try out every row in the table foo? Like, he can't really know the result of foo.a + foo.b so it does it for every single row before checking it for a match inside of stringifiedBar? How would one do that query properly?

23 replies

CC#

•Created by Alta on 4/22/2024 in #help

EF Core Where on multiple fields

Initial question Hi! I'm currently running into an issue where I try and do something along the lines of

void SomeMethod(string argBar, int argBuzz){
//...
efCoreContext.FooTable.Where(f => new { bar = f.bar, buzz = f.buzz } == new { bar = argBar, buzz = argBuzz })
//...
}

void SomeMethod(string argBar, int argBuzz){
//...
efCoreContext.FooTable.Where(f => new { bar = f.bar, buzz = f.buzz } == new { bar = argBar, buzz = argBuzz })
//...
}

The whole idea behind that is to check {f.bar, f.buzz} against a list of {argBar, argBuzz} with something like Where(f => args.Contains(new { bar = f.bar, buzz = f.buzz })) I can't find anything online and I'm quite stuck... (But I'm pretty sure I'm phrasing my google query wrong) A workaround our senior dev provided was to concatenate bar and fuzz into a string Although that works, the query time went absurdly high... If anyone could lead me into the correct direction, that would be highly appreciated 🙏 More context with example and better exaplanation I've got a table with 2 cols Foo and Bar My database has those records

| Foo | Bar |
| 1   | 1   |
| 1   | 2   |
| 1   | 3   |

| Foo | Bar |
| 1   | 1   |
| 1   | 2   |
| 1   | 3   |

code side, I have a list looking like

| Foo | Bar |
| 1   | 1   |
| 2   | 1   |
| 3   | 1   |

| Foo | Bar |
| 1   | 1   |
| 2   | 1   |
| 3   | 1   |

I'd like to fetch every record from my database where a corresponding record is found in my list In this example, only

{
 Foo : 1,
 Bar : 1
}

{
 Foo : 1,
 Bar : 1
}

would be fetched And .Where(x => x.Foo == argFoo && x.Bar == argBar) would work just fine if I only one pair of argument to match the records from But here, I'd like something more akin to a list.Contains(dbRecord) Answer With LinQKit https://discord.com/channels/143867839282020352/1231977108239290418/1231985809209688188

30 replies

CC#

•Created by Alta on 12/20/2022 in #help

❔ How to organise mappers

Hi ! As our app grows, we're confronted to mapping problems We are using two main mapping methodology for dto and entities Either we create a profile for automapper concerned with a specific entity or extension methods say, we have - EntityFoo - FooDTO - FooPostDTO - FooProfile with both configuration for EntityFoo <-> FooDTO and EntityFoo <-> FooPostDTO - FooExtension with several extensions method "Map" with various arguments. The likes of EntityFoo Map(this EntityFoo entity, FooDTO dto); EntityFoo Map(this EntityFoo entity, FooPostDTO dto); Called in the code like so var entity = new EntityFoo().Map(dto); We want to add the field bar to Foo FooPostDTO get updated with bar EntityFoo also But then, we try and use the extension method. No error thrown, but the field is not set. So, this looks like something that shouldn't be done?🤔 But what about automapper? The automapper maps correctly the bar field since it's present on both side for EntityFoo and FooPostDTO But the same thing happens when using automapper for FooDTO It's not updated with the new field, but it won't throw since all assignable field is present on EntityFoo We'll potentially never know that it's not working like intented... So... Looks like it shouldn't be done like this either?🤔 Is there a way to prevent that at coding time ? With compile time error so we CAN NOT make the mistake (rather than letting it pass through and blow up in our face later on) I've been messing a bit with records. Declaring them and their field solely through their constructor Thus, if we update the record, we add the field in the sole constructor of the record It then won't work anywhere if the call isn't updated This looks like a sound idea ! Though, I've been thinking... Why even use a record? Defining specific ctor should do the trick anyway And... "Mapping" through ctor wouldn't have cause all the trouble I explained in this post... So, I was curious. How do you manage mapping on your side? 🤔

9 replies

CC#

•Created by Alta on 12/1/2022 in #help

❔ Conditional data seed

Hi ! Currently working on an api where we're trying to manage two different builds (foo and bar) Foo is using an hellish hybrid database with a bit of SQL (ef core code first), noSQL and a Sequential indexed file database Bar is using a pure SQL database (EF Core code first) We already split the DAL so that services from Foo and Bar uses the same interface (calling their respective databases in their implementations) Foo and Bar are using the same migrations from our repository manager. But now, we want to use a set of initial data with Bar We though about doing a migration and adding some insert in raw sql in them But, considering that Foo and Bar are using the same migrations and that Foo doesn't want those data, we have to find a descent solution that would insert those into Bar while letting Foo untouched (descent meaning " at least maintainable short terms, if possible, maintainable long terms and ideally, not too hard to explain to newcomers in the team") A solution I dug up over that StackOverflow post https://stackoverflow.com/questions/37748859/is-it-possible-to-have-conditional-ef-migration-code sounds doable (but considering it is 6y old and has only 2 answers where the top answer has only 1 upvote,... Yeah, Idk) We'd have the migration in both Foo and Bar But the raw sql in the Up and Down would be wrapped in some preprocessor conditions Like so

protected override void Up(MigrationBuilder migrationBuilder)
  {
#if Bar
    migrationBuilder.Sql(@"
       -- some raw sql where we insert data into the BAR database
    ")
#endif
  }

protected override void Up(MigrationBuilder migrationBuilder)
  {
#if Bar
    migrationBuilder.Sql(@"
       -- some raw sql where we insert data into the BAR database
    ")
#endif
  }

If this solution doesn't look appealing to you... Yeah... To us neither... That's why I'd like to have your opinion on how to tackle that issue, if you will 🙂

6 replies

Gaming

Programming