Possible to set default schema from connection string

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
esankara1
Posts: 10
Joined: Mon 27 Nov 2017 16:42

Possible to set default schema from connection string

Post by esankara1 » Tue 12 Dec 2017 18:50

Hi Team,
We try to set default schema from connection string, but it's not taking it.
Example:
connectionName=UserId=xxx;Password=yyy;Initial Schema=eal;host=localhost;database=db;
If we set default schema through HasDefaultSchema('eal') through modelbuilder, we're getting below error.
Devart.Data.PostgreSql.PgSqlException: current transaction is aborted, commands ignored until end of transaction block
at Devart.Data.PostgreSql.o.a(Boolean A_0, Boolean A_1, Char A_2, Boolean A_3)
at Devart.Data.PostgreSql.v.ae()
at Devart.Data.PostgreSql.i.a(v A_0, Boolean A_1)
at Devart.Data.PostgreSql.i.e()
at Devart.Data.PostgreSql.i.s()
at Devart.Common.DbConnectionInternal.al()
at Devart.Common.DbConnectionFactory.b(DbConnectionBase A_0)
at Devart.Common.DbConnectionClosed.Open(DbConnectionBase outerConnection)
at Devart.Common.DbConnectionBase.Open()
at Devart.Data.PostgreSql.PgSqlConnection.Open()
at Devart.Data.PostgreSql.Entity.ab.a(DbConnection A_0, v& A_1)
at Devart.Data.PostgreSql.Entity.u..ctor(IRelationalConnection A_0)
at lambda_method(Closure , ServiceProvider )
at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService(IServiceProvider provider, Type serviceType)
at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService[T](IServiceProvider provider)
at Microsoft.EntityFrameworkCore.Infrastructure.RelationalServiceCollectionExtensions.<>c.<AddRelational>b__0_11(IServiceProvider p)
at lambda_method(Closure , ServiceProvider )
at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService(IServiceProvider provider, Type serviceType)
at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService[T](IServiceProvider provider)
at Microsoft.EntityFrameworkCore.Internal.DbContextServices.CreateModel()
at Microsoft.EntityFrameworkCore.Internal.LazyRef`1.get_Value()
at lambda_method(Closure , ServiceProvider )
at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService(IServiceProvider provider, Type serviceType)
at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService[T](IServiceProvider provider)
at Microsoft.EntityFrameworkCore.Infrastructure.EntityFrameworkServiceCollectionExtensions.<>c.<AddQuery>b__1_3(IServiceProvider p)
at lambda_method(Closure , ServiceProvider )
at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetService[T](IServiceProvider provider)
at Microsoft.EntityFrameworkCore.Infrastructure.AccessorExtensions.GetService[TService](IInfrastructure`1 accessor)
at Microsoft.EntityFrameworkCore.DbContext.get_QueryProvider()
at Microsoft.EntityFrameworkCore.Internal.InternalDbSet`1.<.ctor>b__3_0()
at Microsoft.EntityFrameworkCore.Internal.LazyRef`1.get_Value()
at Microsoft.EntityFrameworkCore.Internal.InternalDbSet`1.System.Linq.IQueryable.get_Provider()
at System.Linq.Queryable.Any[TSource](IQueryable`1 source, Expression`1 predicate)

Could you please help to rectify this issue

esankara1
Posts: 10
Joined: Mon 27 Nov 2017 16:42

Re: Possible to set default schema from connection string

Post by esankara1 » Tue 12 Dec 2017 18:53

HasDefaultSchema is firing below query,

SET search_path = schema1, It's causing error on multithread environment.

esankara1
Posts: 10
Joined: Mon 27 Nov 2017 16:42

Re: Possible to set default schema from connection string

Post by esankara1 » Wed 13 Dec 2017 08:52

Example Code:
Program.cs

Code: Select all

namespace ConsoleAppTest
{
    public class Program
    {
        public static void Main(string[] args)
        {
            EFCoreDemoContext ctx;
            var optionsBuilder = new DbContextOptionsBuilder<EFCoreDemoContext>();
optionsBuilder.UsePostgreSql("User Id=user;Password=pass@123;Host=localhost;Database=Act1;Initial Schema=Schema1;license key=trial:/home/admin/test/Devart.Data.PostgreSql.key;");
            ctx = new EFCoreDemoContext(optionsBuilder.Options);
        }
    }
}
-----------------------------------------------------------------------------------------------
EFCoreDemoContext.cs

Code: Select all

namespace ConsoleAppTest
{
    public class EFCoreDemoContext : DbContext
    {

        public EFCoreDemoContext(DbContextOptions<EFCoreDemoContext> options)
            : base(options)
        {

        }
        protected override void OnModelCreating(ModelBuilder builder)
        {
          //  builder.HasDefaultSchema("Schema1");
            base.OnModelCreating(builder);
        }
        public DbSet<Patient> Patients { get; set; }

    }
}
--------------------------------------------------------------------------------------------------------------
Patient.cs

Code: Select all

namespace ConsoleAppTest
{
    using System;
    using System.Collections.Generic;

    public partial class Patient
    {
        public Patient()
        {
        }

        public int Id { get; set; }
        public string Account { get; set; }
        public string FirstName { get; set; }
        public string SecondName { get; set; }
        public string Suffix { get; set; }
        public string Prefix { get; set; }
        public Nullable<System.DateTime> DateOfBirth { get; set; }
        public string AddressLine1 { get; set; }
        public string AddressLine2 { get; set; }
        public string City { get; set; }
        public string State { get; set; }
        public string Zip { get; set; }
        public string Cell { get; set; }
        public string HomePhone { get; set; }
        public string OtherPhone { get; set; }
        public string Fax { get; set; }
        public string PrimaryEmail { get; set; }
    }
}
The above code throwing
Devart.Data.PostgreSql.PgSqlException: relation "Patient" does not exist
at Devart.Data.PostgreSql.e.b(v A_0)
at Devart.Data.PostgreSql.v.af()
at Devart.Data.PostgreSql.PgSqlCommand.InternalPrepare(Boolean implicitPrepare, Int32 startRecord, Int32 maxRecords)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Data.PostgreSql.Entity.o.b(CommandBehavior A_0)
at Devart.Common.Entity.cr.d(CommandBehavior A_0)
at Devart.Data.PostgreSql.Entity.o.a(CommandBehavior A_0)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary`2 parameterValues, Boolean closeConnection)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.BufferlessMoveNext(Boolean buffer)
at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.<_ShapedQuery>d__3`1.MoveNext()
at System.Linq.Enumerable.SelectEnumerableIterator`2.MoveNext()
at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source)
at lambda_method(Closure , QueryContext )
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass20_0`1.<CompileQueryCore>b__0(QueryContext qc)
at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source)
at ConsoleAppTest.Program.Main(String[] args)msg

==========================================================

If i set throw Modelbuilder

Code: Select all

        protected override void OnModelCreating(ModelBuilder builder)
        {
            builder.HasDefaultSchema("Schema1");
            base.OnModelCreating(builder);
        }
It's causing problem on multi thread environment

Devart.Data.PostgreSql.PgSqlException: current transaction is aborted, commands ignored until end of transaction block
at Devart.Data.PostgreSql.o.a(Boolean A_0, Boolean A_1, Char A_2, Boolean A_3)
at Devart.Data.PostgreSql.v.ae()
at Devart.Data.PostgreSql.i.a(v A_0, Boolean A_1)
at Devart.Data.PostgreSql.i.e()
at Devart.Data.PostgreSql.i.s()
at Devart.Common.DbConnectionInternal.al()
at Devart.Common.DbConnectionFactory.b(DbConnectionBase A_0)
at Devart.Common.DbConnectionClosed.Open(DbConnectionBase outerConnection)
at Devart.Common.DbConnectionBase.Open()
at Devart.Data.PostgreSql.PgSqlConnection.Open()
at Devart.Data.PostgreSql.Entity.ab.a(DbConnection A_0, v& A_1)
at Devart.Data.PostgreSql.Entity.u..ctor(IRelationalConnection A_0)
at lambda_method(Closure , ServiceProvider )
at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService(IServiceProvider provider, Type serviceType)
at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService[T](IServiceProvider provider)
at Microsoft.EntityFrameworkCore.Infrastructure.RelationalServiceCollectionExtensions.<>c.<AddRelational>b__0_11(IServiceProvider p)
at lambda_method(Closure , ServiceProvider )
at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService(IServiceProvider provider, Type serviceType)
at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService[T](IServiceProvider provider)
at Microsoft.EntityFrameworkCore.Internal.DbContextServices.CreateModel()
at Microsoft.EntityFrameworkCore.Internal.LazyRef`1.get_Value()
at lambda_method(Closure , ServiceProvider )
at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService(IServiceProvider provider, Type serviceType)
at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService[T](IServiceProvider provider)
at Microsoft.EntityFrameworkCore.Infrastructure.EntityFrameworkServiceCollectionExtensions.<>c.<AddQuery>b__1_3(IServiceProvider p)
at lambda_method(Closure , ServiceProvider )
at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetService[T](IServiceProvider provider)
at Microsoft.EntityFrameworkCore.Infrastructure.AccessorExtensions.GetService[TService](IInfrastructure`1 accessor)
at Microsoft.EntityFrameworkCore.DbContext.get_QueryProvider()
at Microsoft.EntityFrameworkCore.Internal.InternalDbSet`1.<.ctor>b__3_0()
at Microsoft.EntityFrameworkCore.Internal.LazyRef`1.get_Value()
at Microsoft.EntityFrameworkCore.Internal.InternalDbSet`1.System.Linq.IQueryable.get_Provider()
at System.Linq.Queryable.Any[TSource](IQueryable`1 source, Expression`1 predicate)

try to set set search_path

Can you please guide how to fix db connection with default schema.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Possible to set default schema from connection string

Post by Shalex » Wed 13 Dec 2017 14:34

Target framework of your project is .NET Core 2, isn't it?

We cannot reproduce the issue with the following code. Please tell us how we should modify it:

Code: Select all

using Devart.Data.PostgreSql;
using Microsoft.EntityFrameworkCore;
using System;
using System.Linq;

namespace ConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            var monitor = new PgSqlMonitor() { IsActive = true };

                EFCoreDemoContext ctx;
                var optionsBuilder = new DbContextOptionsBuilder<EFCoreDemoContext>();
                optionsBuilder.UsePostgreSql(@"User Id=postgres;Password=postgres;Host=localhost;port=5432;Database=postgres;Initial Schema=Schema1;license key=...;");
                ctx = new EFCoreDemoContext(optionsBuilder.Options);

                ctx.Database.EnsureCreated();
                        
                var result = ctx.Patients.ToList();
        }
    }
    public class EFCoreDemoContext : DbContext
    {

        public EFCoreDemoContext(DbContextOptions<EFCoreDemoContext> options)
            : base(options)
        {

        }
        protected override void OnModelCreating(ModelBuilder builder)
        {
            builder.HasDefaultSchema("Schema1");
            base.OnModelCreating(builder);
        }
        public DbSet<Patient> Patients { get; set; }

    }

    public partial class Patient
    {
        public Patient()
        {
        }

        public int Id { get; set; }
        public string Account { get; set; }
        public string FirstName { get; set; }
        public string SecondName { get; set; }
        public string Suffix { get; set; }
        public string Prefix { get; set; }
        public Nullable<System.DateTime> DateOfBirth { get; set; }
        public string AddressLine1 { get; set; }
        public string AddressLine2 { get; set; }
        public string City { get; set; }
        public string State { get; set; }
        public string Zip { get; set; }
        public string Cell { get; set; }
        public string HomePhone { get; set; }
        public string OtherPhone { get; set; }
        public string Fax { get; set; }
        public string PrimaryEmail { get; set; }
    }
}

Post Reply