C#•17mo ago

✅ Efcore help with 2 databases

I'm trying to add a config option for the user to be able to select between psql and SQLite, do I need 2 contexts for this? If so what would be the main differences between the two?

98 Replies

Yawnder•17mo ago

You don't need multiple contexts, but you might need two sets of migration in some cases You can look at this for ideas on the topic: https://blog.jetbrains.com/dotnet/2022/08/24/entity-framework-core-and-multiple-database-providers/

SylveonDekoOP•17mo ago

poggers thx

Jimmacle•17mo ago

you may need separate contexts/configurations depending on what provider specific features you use iirc

Angius•17mo ago

Yeah You'll need to either use the lowest common denominator Or make one context that uses SQLite, and one that takes full advantage of Postgres Or... don't bother with SQLite

Jimmacle•17mo ago

i briefly tried to add sqlite support to make local testing of my application simpler and it was way too much effort docker gang

SylveonDekoOP•17mo ago

the application in question already supports sqlite, we are migrating to psql but want to give selfhosters the option to choose

Angius•17mo ago

Then either use two DbContexts, or forever be gimped by the limitations of SQLite

SylveonDekoOP•17mo ago

speaking of

jcotton42•17mo ago

Postgres is very simple to set up I'd say just require postgres

SylveonDekoOP•17mo ago

and screw over existing selfhosts?

jcotton42•17mo ago

you would need a migration for existing users

SylveonDekoOP•17mo ago

now how would i fix the primary key issue it seems the sequal gem did not transfer it sequel

jcotton42•17mo ago

sequel gem?

SylveonDekoOP•17mo ago

yeah theres a ruby gem that lets you migrate dbs

SylveonDekoOP•17mo ago

SylveonDekoOP•17mo ago

pgloader didnt work

jcotton42•17mo ago

wait why are we suddenly discussing Ruby now?

SylveonDekoOP•17mo ago

so was just mentioning what i used to migrate anyway thats unimportant

jcotton42•17mo ago

... ok show your EF model class

SylveonDekoOP•17mo ago

one moment...

SylveonDekoOP•17mo ago

https://github.com/SylveonDeko/Mewdeko/tree/main/src/Mewdeko.Database/Models

GitHub

Mewdeko/src/Mewdeko.Database/Models at main · SylveonDeko/Mewdeko

Mewdeko. Contribute to SylveonDeko/Mewdeko development by creating an account on GitHub.

SylveonDekoOP•17mo ago

this is the base class for every model and it clearly has a marked key

jcotton42•17mo ago

well you didn't derive from it here

SylveonDekoOP•17mo ago

jcotton42•17mo ago

public class GuildCurrency

SylveonDekoOP•17mo ago

a new class kekw havent even made the migration for it yet

SylveonDekoOP•17mo ago

now to solve this

SylveonDekoOP•17mo ago

should be easier lel

SylveonDekoOP•17mo ago

i have run into my first issue with this...

SylveonDekoOP•17mo ago

or just remove yourself from the post instead of helping that works too. so i have a different issue now on boot, my bot loads a few things into memory, at the same time however because of psql it now takes even longer to boot because ive been needing to use tolist because of a tinput/toutput requires ienumerable error

jcotton42•17mo ago

pardon?

SylveonDekoOP•17mo ago

ok, one sec for example, in a constructor i have these 2 lines: var gc = uow.GuildConfigs.Include(x => x.NsfwBlacklistedTags).Where(x => client.Guilds.Select(socketGuild => socketGuild.Id).Contains(x.GuildId)); blacklistedTags = new ConcurrentDictionary<ulong, HashSet<string>>( gc.ToDictionary( x => x.GuildId, x => new HashSet<string>(x.NsfwBlacklistedTags.Select(y => y.Tag)))); if i load them normally right now i get an error with psql that i didnt have with sqlite waiting for it to start one sec... Exception has been thrown by the target of an invocation. ---> System.ArgumentException: Both TInput and TOutput must be IEnumerable it gives me that. now when i add the tolist, it fixes it, but causes massive performance issues.

Jimmacle•17mo ago

that's way too much to be happening in a constructor, plus it prevents you from taking advantage of async

SylveonDekoOP•17mo ago

tru, a lot of the bot needs to be refactored but

Jimmacle•17mo ago

well, we need the full error to do much

SylveonDekoOP•17mo ago

sec

SylveonDekoOP•17mo ago

message.txt

SylveonDekoOP•17mo ago

beeg error

Jimmacle•17mo ago

that error looks like it's from a completely different part of your code Mewdeko.Modules.Administration.Services.GuildTimezoneService..ctor

SylveonDekoOP•17mo ago

it has the same thing, tolist but cant i just use asenumerable? i kinda forgot that exists.

jcotton42•17mo ago

that will force client-side enumeration

SylveonDekoOP•17mo ago

hmm

jcotton42•17mo ago

that is, EF will be forced to execute your query, pulling everything into memory

SylveonDekoOP•17mo ago

so not much better than tolist at all so heres the thing, on boot, it sorts a few things for each service/module in a dictionary or concurrenthashset even concurrentdictionary at times psql doesnt like that, but i had no issues on sqlite so im at a bit of a loss at what to do

Jimmacle•17mo ago

i don't know how much EF Core likes this being in the where expression itself client.Guilds.Select(socketGuild => socketGuild.Id).Contains(x.GuildId) i'm going to guess something in there is causing problems since the rest should be getting evaluated client side based on what's on your github maybe try turning the client's guild IDs into a list first?

SylveonDekoOP•17mo ago

Would that really cause an issue? .... My bot is in 10k guilds

Jimmacle•17mo ago

idk, that's just my suggestion for something to try

SylveonDekoOP•17mo ago

Obviously not testing on production but

Jimmacle•17mo ago

well it's in 0 if it doesn't work

SylveonDekoOP•17mo ago

Production is in 10k guilds

Jimmacle•17mo ago

okay, use an array pool in prod

SylveonDekoOP•17mo ago

Alright

Jimmacle•17mo ago

is this bot sharded? 10k seems steep for a single instance

SylveonDekoOP•17mo ago

It is ofc 5 shards Right now I'm testing a local db/program copy so this:

var guildIds = client.Guilds.Select(socketGuild => socketGuild.Id).ToList();
        timezones = uow.GuildConfigs
            .Where(x => guildIds.Contains(x.GuildId))
            .Select(GetTimzezoneTuple)
            .Where(x => x.Timezone != null)
            .ToDictionary(x => x.GuildId, x => x.Timezone)
            .ToConcurrent();

var guildIds = client.Guilds.Select(socketGuild => socketGuild.Id).ToList();
        timezones = uow.GuildConfigs
            .Where(x => guildIds.Contains(x.GuildId))
            .Select(GetTimzezoneTuple)
            .Where(x => x.Timezone != null)
            .ToDictionary(x => x.GuildId, x => x.Timezone)
            .ToConcurrent();

Gives the same error

Jimmacle•17mo ago

understandable because that code is not related to the error you shared

SylveonDekoOP•17mo ago

It is, it gives the same error same stack trace, just different location

Jimmacle•17mo ago

nvm, reading hard

SylveonDekoOP•17mo ago

Lol Happens

Jimmacle•17mo ago

well, based on github GetTimeZoneTuple is not a function that can be translated to SQL still not directly related to your error, but it shouldn't work TimeZoneInfo.FindSystemTimeZoneById doesn't have a translation according to https://www.npgsql.org/efcore/mapping/translations.html

SylveonDekoOP•17mo ago

hmm so what do i do

Jimmacle•17mo ago

dunno, i'm surprised it's not complaining about a translation failure before getting to this point

SylveonDekoOP•17mo ago

same its actually complaining in my concurrenthashset class now

SylveonDekoOP•17mo ago

message.txt

SylveonDekoOP•17mo ago

i mean the other option is raw queries lmao

Jimmacle•17mo ago

maybe

SylveonDekoOP•17mo ago

i dont see any other option that wont kill startup times

Jimmacle•17mo ago

ultimately something in your query isn't translating correctly, but i don't know enough about ef/npgsql internals to know exactly

SylveonDekoOP•17mo ago

hmm oof simple

public static IEnumerable<GuildConfig> GetActiveConfigs(this DbSet<GuildConfig> set, IEnumerable<ulong> guildIds)
   {
       var sqlQuery = $"SELECT * FROM GuildConfigs WHERE GuildId = ANY ('{{{string.Join(",", guildIds)}}}')";

       return set.FromSqlRaw(sqlQuery);
   }

public static IEnumerable<GuildConfig> GetActiveConfigs(this DbSet<GuildConfig> set, IEnumerable<ulong> guildIds)
   {
       var sqlQuery = $"SELECT * FROM GuildConfigs WHERE GuildId = ANY ('{{{string.Join(",", guildIds)}}}')";

       return set.FromSqlRaw(sqlQuery);
   }

tho i may have to do "" around the table/column names because psql

Jimmacle•17mo ago

i'm gonna sql inject your guild when

SylveonDekoOP•17mo ago

lel

SylveonDekoOP•17mo ago

perfect

SylveonDekoOP•17mo ago

.... not perfect im getting a formatting error love it god fucking damn it same error enumerable headdesk

anyone at all shed some light? ive been at this since 12am yesterday i really want to avoid using tolist

SylveonDekoOP•17mo ago

help. i really want to avoid doing this.

Angius•17mo ago

Why do you not want an IEnumerable?

SylveonDekoOP•17mo ago

because doing tolist or doing asenumerable caches it this works fine on sqlite var gc = uow.GuildConfigs.Include(x => x.NsfwBlacklistedTags).Where(x => client.Guilds.Select(socketGuild => socketGuild.Id).Contains(x.GuildId)); blacklistedTags = new ConcurrentDictionary<ulong, HashSet<string>>( gc.ToDictionary( x => x.GuildId, x => new HashSet<string>(x.NsfwBlacklistedTags.Select(y => y.Tag)))); why is pgsql any different?? sqlite, the database where you cant even change the primary key

Angius•17mo ago

So why, then, you say you want to avoid .ToList() as well...?

SylveonDekoOP•17mo ago

tell me why loading 20k records into memory is bad?

Angius•17mo ago

It takes a lot of memory

SylveonDekoOP•17mo ago

and a lot of startup time

Angius•17mo ago

So you don't want an enumerable, you don't want a list...

SylveonDekoOP•17mo ago

it was working with sqlite there has to be something that we are not seeig here

Angius•17mo ago

Lemme format your code properly first so I can actually see what's going on there

SylveonDekoOP•17mo ago

sec

public GuildTimezoneService(DiscordSocketClient client, Mewdeko bot, DbService db)
   {
       using var uow = db.GetDbContext();
       timezones = uow.GuildConfigs.Where(x => client.Guilds.Select(socketGuild => socketGuild.Id).Contains(x.GuildId))
           .Select(GetTimzezoneTuple)
           // ReSharper disable once ConditionIsAlwaysTrueOrFalseAccordingToNullableAPIContract
           .Where(x => x.Timezone != null)
           .ToDictionary(x => x.GuildId, x => x.Timezone)
           .ToConcurrent();

       var curUser = client.CurrentUser;
       if (curUser != null)
           AllServices.TryAdd(curUser.Id, this);
       this.db = db;

       bot.JoinedGuild += Bot_JoinedGuild;
   }

public GuildTimezoneService(DiscordSocketClient client, Mewdeko bot, DbService db)
   {
       using var uow = db.GetDbContext();
       timezones = uow.GuildConfigs.Where(x => client.Guilds.Select(socketGuild => socketGuild.Id).Contains(x.GuildId))
           .Select(GetTimzezoneTuple)
           // ReSharper disable once ConditionIsAlwaysTrueOrFalseAccordingToNullableAPIContract
           .Where(x => x.Timezone != null)
           .ToDictionary(x => x.GuildId, x => x.Timezone)
           .ToConcurrent();

       var curUser = client.CurrentUser;
       if (curUser != null)
           AllServices.TryAdd(curUser.Id, this);
       this.db = db;

       bot.JoinedGuild += Bot_JoinedGuild;
   }

same thing, works with sqlite throws the same error with postgres System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.ArgumentException: Both TInput and TOutput must be IEnumerable at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.ValueConversion.NpgsqlArrayConverter`2

Angius•17mo ago

What's GetTimezoneTuple?

SylveonDekoOP•17mo ago

private static (ulong GuildId, TimeZoneInfo? Timezone) GetTimzezoneTuple(GuildConfig x)
    {
        TimeZoneInfo tz;
        try
        {
            tz = x.TimeZoneId == null ? null : TimeZoneInfo.FindSystemTimeZoneById(x.TimeZoneId);
        }
        catch
        {
            tz = null;
        }

        return (x.GuildId, Timezone: tz);
    }

private static (ulong GuildId, TimeZoneInfo? Timezone) GetTimzezoneTuple(GuildConfig x)
    {
        TimeZoneInfo tz;
        try
        {
            tz = x.TimeZoneId == null ? null : TimeZoneInfo.FindSystemTimeZoneById(x.TimeZoneId);
        }
        catch
        {
            tz = null;
        }

        return (x.GuildId, Timezone: tz);
    }

but i think thats irrelavant because it has the same error with todictionary

Angius•17mo ago

Yeah, well, no chance in hell this will translate to SQL

SylveonDekoOP•17mo ago

I mean, it worked in SQLite Lol

Angius•17mo ago

Maybe the SQLite driver does the untranslatable things on the client by default

SylveonDekoOP•17mo ago

.... so it passes when i remove the where with the guildid

SylveonDekoOP•17mo ago

SylveonDekoOP•17mo ago

but not with. sorry the contains

SylveonDekoOP•17mo ago

wat

SylveonDekoOP•17mo ago

message.txt

SylveonDekoOP•17mo ago

var guildConfigIds = uow.GuildConfigs.AsNoTracking().AsEnumerable().Where(x => bot.Client.Guilds.Select(socketGuild => socketGuild.Id).Contains(x.GuildId)).Select(x => x.Id);
           subs = uow.GuildConfigs
               .AsQueryable()
               .Where(x => guildConfigIds.Contains(x.Id))
               .Include(x => x.FeedSubs)
               .AsEnumerable()
               .SelectMany(x => x.FeedSubs)
               .GroupBy(x => x.Url.ToLower())
               .ToDictionary(x => x.Key, x => x.ToHashSet())
               .ToConcurrent();

var guildConfigIds = uow.GuildConfigs.AsNoTracking().AsEnumerable().Where(x => bot.Client.Guilds.Select(socketGuild => socketGuild.Id).Contains(x.GuildId)).Select(x => x.Id);
           subs = uow.GuildConfigs
               .AsQueryable()
               .Where(x => guildConfigIds.Contains(x.Id))
               .Include(x => x.FeedSubs)
               .AsEnumerable()
               .SelectMany(x => x.FeedSubs)
               .GroupBy(x => x.Url.ToLower())
               .ToDictionary(x => x.Key, x => x.ToHashSet())
               .ToConcurrent();

Whats wrong here? so the solution was to use:

.Where(x => (int)(x.GuildId / (ulong)Math.Pow(2, 22) % (ulong)creds.TotalShards) == client.ShardId)

.Where(x => (int)(x.GuildId / (ulong)Math.Pow(2, 22) % (ulong)creds.TotalShards) == client.ShardId)

or at least so far thats the solutuon

SylveonDekoOP•17mo ago

something is very wrong here.

SylveonDekoOP•17mo ago

i went from 15 seconds load time to a whole 2 minutes.

SylveonDekoOP•17mo ago

Accord•17mo ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

Gaming

Programming

✅ Efcore help with 2 databases