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
Possible to set default schema from connection string
Re: Possible to set default schema from connection string
HasDefaultSchema is firing below query,
SET search_path = schema1, It's causing error on multithread environment.
SET search_path = schema1, It's causing error on multithread environment.
Re: Possible to set default schema from connection string
Example Code:
Program.cs
-----------------------------------------------------------------------------------------------
EFCoreDemoContext.cs
--------------------------------------------------------------------------------------------------------------
Patient.cs
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
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.
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; }
}
}
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);
}
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.
Re: Possible to set default schema from connection string
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:
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; }
}
}