Devart Forums

damon.cognito

That's great, thanks - it's a huge problem for us.

damon.cognito

I am dumping a schema using PgSqlDump but at the top of the dump it has the command

SET default_with_oids = false;

The problem is that the dump does not put 'WITH OID = true' on those tables created with an OID field; I think this is a defect as it is not producing a full back up. Or have I got a setting wrong in the dump command?

There may be an argument that the default line should not be there at all as it should use the default as set in the postgres.conf file.

I am using 5.0.1479.0, postgresql 9.3, VS2015 Professional on Windows

cheers, Damon.

Code: Select all

                        using (var schemaBackup = new PgSqlDump()
                        {
                            Connection = DBAccess.GetConnection(),
                            QuoteIdentifier = true,
                            Mode = DumpMode.Schema,
                            GenerateHeader = false,
                            UseMultirowSyntax = true,
                            ObjectTypes = PgSqlDumpObjects.Schemas | PgSqlDumpObjects.Sequences | PgSqlDumpObjects.Tables | PgSqlDumpObjects.Views
                        })
                        {
                            string name = "dump_schema.backup");
                            schemaBackup.Backup(name);
                        }

damon.cognito

In a previous response to me to get around a problem with poor information coming from the EF tree, you suggested writing an optimised SQL and storing it against the model. I really don't like this as one of the main reason we use EF is to make us aware of any issues in the code referencing renamed/removed tables/fields, or simple typos.

What would be good would be either
1. that when model is saved, each sql command text entries is run (each in a transaction which is aborted so as to not alter the DB) and any errors reported
2. You could write hybrid sqls command text entries using entity model substitutes, like

Code: Select all

select <Table1.Field1> from <entity.Table1> left outer join <entity.Table2> on <Table1.Field2> = <Table2.Field2> where <Table2.Field1> = 1

and for then to be validated on the save (and model save). If it had context help on the strong typed fields even better.

The second option is a bit of fantasy but the first should be fairly easy and quick to implement (together with a 'validate on model save' check box on sql command text entries dialog)?

damon.cognito

Found a bit of a performance issue with the generated sql in some instances. I've reproduced on a sample database. The LINQ is:

Code: Select all

var dt = new DateTime(2005, 10, 1);
var q1 = (from mh in entity.Head
            select new
            {
                t = (decimal?)(mh.Accs.Bal1 - mh.Bills.Where(o => o.StampDate > dt).Sum(o => o.Amount - o.Total ?? 0M)) ?? 0M,
                a = (decimal?)(mh.Accs.Bal2 - mh.Bills.Where(o => o.StampDate <= dt).Sum(o => o.Amount)) ?? 0M,
                mh.OIDHead
            }).ToList();

The resultant sql carries out the Sums on TblBills twice each making 4 in total. The issue appears to be cuased by the ?? 0M on the end. As soon as these are removed the sql has the expected two sums (but I do need them as it must return 0 not null if there are no records).

If I then add a where clause based on the calculated variables like so

Code: Select all

var q1 = (from mh in entity.Head
            select new
            {
                t = (decimal?)(mh.Accs.Bal1 - mh.Bills.Where(o => o.StampDate > dt).Sum(o => o.Amount - o.Total ?? 0M)) ?? 0M,
                a = (decimal?(mh.Accs.Bal2 - mh.Bills.Where(o => o.StampDate <= dt).Sum(o => o.Amount)) ?? 0M,
                mh.OIDHead
            }).Where(o => o.t != 0M || o.a != 0M).ToList();

It is doubled, so that the the total amount of sums is 8 instead of 2. The example is trivial but when scaled up it is too slow even with indices. Is there any optimisation you can do your side to the SQL or is this a tree problem?

Head -> Accs is a 1 -> 1
Head -> Bills is a 1 -> *

I can send you the schema if you need it.

Thanks.

VS2013, EF6.01, Devart 7.4.592

damon.cognito

It works but not completely as far as I can see. My project uses the .6 version of the Devart.Data.PostgreSql.Entity dll but the devart update tool/wizard always replaces this with the .0 (and updates the app.config). It is nothing new, it has always done it - but given the number of projects we have it's now becoming really annoying!

I think the problem is that when the devart update is installed it uninstalls the old version, so in c# (VS2013) the dlls is shown missing with no version. However, when the update wizard finds this (which will be the case most of the time) it should look up the app.config file to check the version used?

damon.cognito

I have a tiny project that runs housekeeping etc outside the main product that uses EF6. Since updating to 7.4.592 from 7.3.457 the size of the compiled application has gone from 350Kb to 5,400Kb. Nothing else has changed in the project - is there a reason for this happening please?

VS2013 c# EF6

damon.cognito

That's great; good work guys!

damon.cognito

Pinturiccio wrote:We could not reproduce the issue neither with version 7.3.457 nor with the latest one. Please send us the script of the database, for which you perform backup, in order to reproduce the issue.

I'm not allowed to send you the database, but given it only happens in a small amount of cases it is not assured to happen anyway. Can you confirm the order you get the Views back in to be able to dump them please? It might shed some light on this...

cheers!

damon.cognito

We are using PgSqlDump to back up a schema (without indices or constraints) but it is erroring on restore on some but not databases (that have the same schema).

Code: Select all

                    using (var schemaBackup = new PgSqlDump()
                    {
                        Connection = DBAccess.GetConnection(),
                        QuoteIdentifier = true,
                        Mode = DumpMode.Schema,
                        GenerateHeader = false,
                        UseMultirowSyntax = true,
                        ObjectTypes = PgSqlDumpObjects.Schemas | PgSqlDumpObjects.Sequences | PgSqlDumpObjects.Tables | PgSqlDumpObjects.Views
                    })
                    {
                        string name = Path.Combine(GetWorkingFolder(), "dump_schema.backup");
                        schemaBackup.Backup(name);
                        ...
                    }

One of the views (wipTot) references another view (wip), but looking at the script produced by PgSqlDump, the view wip is generated it is created after wipTot (I've run the script in pgadmin and it does the same).

I am guessing that you use pg_views to retrieve the views to dump? If that is the case, that would explain the problem as that view is unordered and so there is no guarantee of which order they will be displayed. The views need to be dumped in the order they were created to solve the dependency problem.

Devart.Data.PostgreSql.Entity, Version=7.3.457.6
VS 2013
Postgresql 9.3.9

cheers!

damon.cognito

Hi, thanks for the reply. The reason I was using parameters for the password is because it may have characters in it that cause a problem in the command string (", /, '). I now understand the role is not a parameter but is the password not a parameter?

damon.cognito

I'm trying to change the password of a login role ('test'):

Code: Select all

            using (var command = new PgSqlCommand("alter role @user test with password @pw", MyConnection))
            {
                command.Parameters.AddWithValue("@user", user);
                command.Parameters.AddWithValue("@pw", pw);
                result = (command.ExecuteNonQuery() == -1);
            }

But this fails with: syntax error at or near \"$1\"

I tried hardcoding the password (i.e. i.e. alter role @user test with 'test') and this fails too. It works fully hardcoded (i.e. alter role test with 'test').

c#, VS2013, latest dotConnect

damon.cognito

I tried your suggestion which is quite neat except it took over 10 minutes to 'select all' and another couple of minutes once I changed the setting (and this is not a slow machine). So I'll stick to an external script for now and post the suggestion at the link you provided. Thanks for your help.

cheers!

damon.cognito

Shalex wrote:You are working with the Entity Framework model via Entity Developer, aren't you? Please select all necessary class properties in Model Explorer and set "Concurrency Mode=Fixed" for all of them at once.

Yes I am, but I just need to check what you are suggesting... are you suggesting expanding each of the 300+ class nodes, then selecting each individual field in each node?

Shalex wrote:
damon.cognito wrote:Secondly, the currency is at the record level, is there any support for it at the field level? ...
The expression tree for the WHERE clause is constructed in EF engine, then it is passed to our EF provider for generating database specific SQL. Please address this suggestion (expression tree optimization) to Microsoft EF developers.

JICx
There is another approach to implement concurrency processing. Create a timestamp column in your table and refresh its value each time the record is updated.

Thanks for the suggestion but that row level again, not field level; if I'm just updating field2 I don't care if field1 has changed (and visa versa). But if that is controlled by MS then I guess there is not much hope!

damon.cognito

Brilliant thanks - knew I was missing something. Just in case anyone else is looking at this, the include appears to have to be before the Where.

cheers!

damon.cognito

Our database is a large multi-user thing so we basically want to set Currency to fixed on every field. Is there a way to do this when creating the model other than writing a script to go through the model and making changes?

Secondly, the currency is at the record level, is there any support for it at the field level? For example, take

Table1 (id, field1, field2, field3)
(1, 1, 1, 1)
(2, 2, 2, 2)

If we load the first record (1, 1, 1) and only change field2 to 2 I want the sql to be produced to be:

Code: Select all

update Table1 set field2 = 2 where id = 1 and field2 = 1

not

Code: Select all

update Table1 set field2 = 2 where id = 1 and field1 = 1 and field2 = 1 and field3 = 1

i.e. it is valid for other fields to have been changed, just not the ones I have changed.

Devart Forums

Search found 50 matches

Re: PgSqlDump - SET default_with_oids = false;

PgSqlDump - SET default_with_oids = false;

SQL stored against model

Multiple sums in sql when not required

Update wizard

Massive increase in compiled size

Re: PgSqlDump

Re: PgSqlDump

PgSqlDump

Re: command parameters

command parameters

Re: Concurrency

Re: Concurrency

Re: EF LINQ to PostgreSQL Include

Concurrency