Multiple db context and keeping single resource

[AnushreeSoman21]

SUMMARY

I would want to use a multiple db context approach along with JSON API dot net core and in my case, I want to use 2 db contexts 1 for Postgresql and 1 for sql. I want to use the same entities but with some differences in db types as used for Postgresql / SQL Db

STEPS TO REPRODUCE

With the below code, If I want to use a single resource 'ResourceA' in both db contexts, the program gives an error and does not allow me to add same resource in the Program.cs file

// Program.cs
builder.Services.AddDbContext(options => options.UseNpgSql("Data Source=A.db"));
builder.Services.AddDbContext(options => options.UseSqlServer("Data Source=B.db"));

builder.Services.AddJsonApi(dbContextTypes: new[] { typeof(DbContextA), typeof(DbContextB) });

builder.Services.AddScoped<IResourceRepository, DbContextARepository>();
builder.Services.AddScoped<IResourceRepository, DbContextBRepository>();

Can I able to use the same Resource/Entity with multiple db contexts and just ignore the properties that do not support them?

Can you please help me with this with some examples?
Note: I do not want to change the API endpoint for the ResourceA with 2 db contexts.

VERSIONS USED

• JsonApiDotNetCore version: 5.5.1
• ASP.NET Core version: 8
• Entity Framework Core version: 8
• Database provider: PgSql and SQL

[bkoelman]

Why are you trying to do this, and how do you expect it to work? What error (with stack trace) are you getting? You can't register the exact same repository interface multiple times, one of them will be ignored. There can only be one repository per resource type. If there would be two, both inserting a row and returning the database-generated ID, which one should be sent in the response? It doesn't make sense. Also the operation isn't atomic, ie the first insert succeeds but the second fails.

[wayne-o]

I think the transactional outbox pattern is what's needed here? https://microservices.io/patterns/data/transactional-outbox.html

…

On Fri, 19 Apr 2024 at 14:52, Bart Koelman ***@***.***> wrote: Why are you trying to do this, and how do you expect it to work? What error (with stack trace) are you getting? You can't register the exact same repository interface multiple times, one of them will be ignored. There can only be one repository per resource type. If there would be two, both inserting a row and returning the database-generated ID, which one should be sent in the response? It doesn't make sense. Also the operation isn't atomic, ie the first insert succeeds but the second fails. — Reply to this email directly, view it on GitHub <#1532 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AABLPDDRKS7J4OIW3WD72CDY6EOSTAVCNFSM6AAAAABGO5YXAOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDANRWGYZTMOJZGA> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[AnushreeSoman21]

Hi @bkoelman,

Thanks for your response.

my requirement scenario here is,
I want to support two databases 1 from SQL and another from PgSql. At both places, entities will be the same with some minimal db-type differences.

Users can choose the DB type at run time. Can you please, let me know if this can be achievable using the JANDC package?

Thanks in advance.

[bkoelman]

What do you mean with "at runtime"? Choose once at app startup, or choose per request? Please be specific, "I want to support" doesn't clarify how you expect the system to behave. Provide use cases with examples, if needed.

Is this issue related to #1533?

[AnushreeSoman21]

Hi @bkoelman ,

I am providing an example here,

Entity :

public class Book
{
public string Name {get;set;}
public string Author {get; set;}
}

I have two db contexts one connects to SQL and another to PgSql

First (SQL Db context) :

public class SqlContext: DbContext
{
  public SQLContext(DbContextOptions<SQLContext> options) : base(options)
  {
  }
public DbSet<Book> Books{get;set;}
}

Second (Pg SQL Context) :

public class PgSqlContext: DbContext
{
public PgSqlContext(DbContextOptions<PgSqlContext> options) : base(options)
{
}
public DbSet<Book> Books{get;set;}
}

Program.cs

builder.Services.AddDbContext<PgSqlContext>(options =>
{
    options.UseNpgsql(builder.Configuration.GetConnectionString("PgSqlConnection"));
});

builder.Services.AddDbContext<SQLContext>(options =>
{
    options.UseSqlServer(builder.Configuration.GetConnectionString("SqlConnection"));
});
builder.Services.AddJsonApi(options =>
{
    options.IncludeExceptionStackTraceInErrors = true;

    options.Namespace = "api";
    options.IncludeTotalResourceCount = true;
    options.AllowUnknownQueryStringParameters = true;
    options.SerializerOptions.WriteIndented = true;
    options.SerializerOptions.Converters.Add(new JsonStringEnumConverter());
}, dbContextTypes: new[]
{   typeof(PgSqlContext),   
    typeof(SQLContext)
});

var app = builder.Build();
app.UseJsonApi();

Now when I am hitting any request, if I provide which db I want to choose (i.e. Choose per request) the system will select the dbcontext and its repository dynamically to fetch the data or to POST/PATCH/Delete the data.
for eg. When I inform my API that I want to choose a PgSql connection, then the API should provide results from PgSql.

I hope this will give you a clear idea.

Note: My scenario is different than #1533 as they have two different entities in 2 db contexts. I want to use the same entity at both places.

Thanks in advance.

[bkoelman]

Thanks for the details, I understand now. I've provided a working sample in #1535. Hope this helps.

[AnushreeSoman21]

Hi @bkoelman ,

Thanks for your help and prompt response on this.
One more thing I want to check with you instead of the [Resource] attribute on an entity if I want to write my controller methods

For eg.

public class BooksController : BaseJsonApiController<Book, int>
{
    public BooksController(IJsonApiOptions options, IResourceGraph resourceGraph,
        ILoggerFactory loggerFactory, IResourceService<Book, int> resourceService)
        : base(options, resourceGraph, loggerFactory, resourceService)
    {
    }
    [HttpGet]
    public override Task<IActionResult> GetAsync(CancellationToken cancellationToken)
    {
        return base.GetAsync(cancellationToken);
    }
}

Will This work?

Thanks

[bkoelman]

Yes

[AnushreeSoman21]

Hi @bkoelman ,

I have tried the example you have provided but when I am adding class DbAwareLinkBuilder I am not able to resolve the reference for 'IDocumentDescriptionLinkProvider'.
public sealed class DbAwareLinkBuilder( IJsonApiOptions options, IJsonApiRequest request, IPaginationContext paginationContext, IHttpContextAccessor httpContextAccessor, LinkGenerator linkGenerator, IControllerResourceMapping controllerResourceMapping, IPaginationParser paginationParser, IDocumentDescriptionLinkProvider documentDescriptionLinkProvider) : LinkBuilder(options, request, paginationContext, httpContextAccessor, linkGenerator, controllerResourceMapping, paginationParser, documentDescriptionLinkProvider)

I am getting the error as below,
"The type or namespace name 'IDocumentDescriptionLinkProvider' could not be found (are you missing a using directive or an assembly reference?)"

I can see this interface is under the namespace "JsonApiDotNetCore.Serialization.Response" but after adding that in using statement also this is not resolving the reference.

One more thing under package JANDC 5.5.1 - "LinkBuilder" class constructor does not have IDocumentDescriptionLinkProvider documentDescriptionLinkProvider as input parameter.

Can you please help me on this. I have created project in .NET8 and JANDC 5.5.1

Thanks in advance.

[bkoelman]

You can just leave it out. It hasn't been released yet.