Devart Forums

The following loop will break after max(NumberOfRows, 100) iterations although it should finish after NumberOfRows (Item's number of rows) times:
foreach (var q in new DevartDataContext(ctx).GetTable().Select(x => x))
Item item = Item.create(ctx, q.Key);
I use version 4.90.124 and am almost sure that in version 4.55 the loop finished correctly using the same code.
The statement in the loop body is a database read using the same connection. It needn't be in relation with the iteration table.

I am always afraid to install a new version because of such problems. This change caused data loss in our production database and interrupted operation for hours.

After installing 4.55.44, my production application crashed. After reinstalling 4.55.39, the problem disappeared.

Here is the code that works with .39 (okSQL == true and okLINQ == true) and doesn't with .44 (okSQL == true but okLINQ == false). Here we use our framework but I think the code is understandable.

cn.executeNonQuery("INSERT INTO APFOB(Fob) VALUES ('Z')");

bool okSQL = false;
cn.CommandTextClearParameters = "SELECT * FROM APFOB WHERE Fob='Z'";
if (cn.readAndReturnSet().Count == 1)
okSQL = true;

bool okLINQ = false;
var cnt = (from q in new DevartDataContext(ctx).GetTable()
where q.Key == "Z"
select q).Count();
if (cnt == 1) okLINQ = true;

It seems that LINQ data context uses a new connection so it doesn't see the changes that happen in current transaction. I reported a similar problem before and it was solved.

I am hesitating because it will take some tome to make the separate project. Because of performance I decided not to use LINQ in complex queries and in simple ones AsEnumerable() solves the problem.

The compiled query is much faster. For my example 0.391 sec. (LINQ) compared to 0.086 sec. (plain SQL). However, that it still 4.5 times more slowly and is awkward to write the delegates. A metagenerated data tier is significantly faster.
200 row fetches for one procedure are seldom needed, that is only for testing purposes. I wanted to say that a 2, 5 or 10 times more slowly data access may not be acceptable. For my application, it isn't.

This was a bad example because DocumentNumber overrides OrderNumber, but order by OrderNumber doesn't work either although it is defined with an attribute: [Column (Storage = "orderDate", Name = "Orddate")]. I assume that the definition is all right because other operations work with columns that cannot be a part of "order by" clause. Or is there an error here?
Error stack for OrderNumber seems to be the same as for DocumentNumber.

I used 4.55.39. Here is the stack:
at Devart.Data.Linq.Provider.Query.p.c.a(a0 A_0, MemberInfo A_1)
at Devart.Data.Linq.Provider.Query.p.c.b(bc A_0)
at Devart.Data.Linq.Provider.Query.p.c.a(bc A_0)
at Devart.Data.Linq.Provider.Query.SqlVisitor.a(SqlNode A_0)
at Devart.Data.Linq.Provider.Query.SqlVisitor.c(SqlExpression A_0)
at Devart.Data.Linq.Provider.Query.SqlVisitor.a(o A_0)
at Devart.Data.Linq.Provider.Query.SqlVisitor.a(SqlNode A_0)
at Devart.Data.Linq.Provider.Query.SqlVisitor.c(SqlExpression A_0)
at Devart.Data.Linq.Provider.Query.SqlVisitor.e(bo A_0)
at Devart.Data.Linq.Provider.Query.p.c.a(bo A_0)
at Devart.Data.Linq.Provider.Query.SqlVisitor.a(ax A_0)
at Devart.Data.Linq.Provider.Query.p.c.a(ax A_0)
at Devart.Data.Linq.Provider.Query.SqlVisitor.a(SqlNode A_0)
at Devart.Data.Linq.Provider.Query.SqlVisitor.e(SqlNode A_0)
at Devart.Data.Linq.Provider.Query.SqlVisitor.e(bo A_0)
at Devart.Data.Linq.Provider.Query.p.c.a(bo A_0)
at Devart.Data
.Linq.Provider.Query.SqlVisitor.a(ax A_0)
at Devart.Data.Linq.Provider.Query.p.c.a(ax A_0)
at Devart.Data.Linq.Provider.Query.SqlVisitor.a(SqlNode A_0)
at Devart.Data.Linq.Provider.Query.p.a(SqlNode A_0, SqlFactory A_1, TypeSystemProvider A_2, ao A_3, a A_4)
at Devart.Data.Linq.Provider.DataProvider.a(c A_0, Type A_1, SqlNode A_2, IList`1 A_3)
at Devart.Data.Linq.Provider.DataProvider.a(Expression A_0)
at Devart.Data.Linq.Provider.DataProvider.h(Expression A_0)
at Devart.Data.Linq.DataQuery`1.i()
at Microline.Test.Test.test46() in C:\Users\Administrator\Source\C#\MOL\Test\Test.cs:line 1130
at Microline.Test.Test.render() in C:\Users\Administrator\Source\C#\MOL\Test\Test.cs:line 1110

I thought the actual read would be deferred until the second command because the table wasn't used in the first one.
After using the code you proposed, I got the similar results. For a different table 200 LINQ reads took 2.308 sec. and plain SQL spent 0.078 sec., 30 times difference.
How about LINQ queries, do they have to last 2-10 times longer?

Our 2-core web server with 2 GB RAM worked fine. Then I decided to use LINQ entity classes to access database.
After the installation, the web server was immediately overloaded. So I increased number of CPU cores to 8 and RAM to 12 GB. It was no solution because CPU load was constantly between 95 and 100% and 12 GB of RAM wasn't enough.
After a little profiling, I found that LINQ queries work between 2 and 10 times more slowly than plain text SQL. Furthermore, the second line in this code worked 120 times longer:
Table po = dataContext.GetTable();
PurchaseOrder single = po.Single(c => c.Key == key);
After metagenerating the code and changing the 2. line, the performance penalty decreased to 2 times, compared to plain SQL SELECT.
Now I have 8 idle cores, working seldom at more than 10% and 10 GB RAM too much.
Is that normal?

1. Order by a column that is not a PK (DocumentNumber):

(from q in SalesOrders
where q.Id == 1
select q).OrderBy(x => x.DocumentNumber)

generates an error ("Specified method is not supported.").

2. The same code ordered by a PK (Id) generates no error:

(from q in SalesOrders
where q.Id == 1
select q).OrderBy(x => x.Id)

3. The result is the same when using from...where...orderby...select query.

4. (from q in SalesOrders
where q.Id == 1
select q).AsEnumerable().OrderBy(x => x.DocumentNumber)
always works.

How can I use remote ORDER BY? Ordering by a not-PK column worked fine up to a few versions ago.

Is there a simpler way? Why don't you omit the sequence column from the generated SQL INSERT statement?

Thanks for the solution. After installing .Net 3.5 SP1 the problem disappeared.

I confirm that the problem has been fixed.

My development web and database servers are Windows 2008.
Our production web server is Windows 2003 and Postgres (the same version) runs on Ubuntu.
The following code in development environment finds a record, which exists in both cases, but on production IIS the result is a "Sequence contains no matching element" exception.

...
var all = dataContext.GetTable();
return all.Single(c => c.ItemKey == "value1" && c.LocationKey == "value2");
...

A simple (1 column) primary key works always fine, but all of the few examples of a 2-column PK I tried used to raise exceptions in Windows 2003.
I use the last (.22) Devart version.

I installed .20 version instead of .16. The application crashed. The problem is for example in this sequence:

//... dataContext instantiated using an open connection
Devart.Data.Linq.Table all = dataContext.GetTable();
return all.Single(c =>true);
//... here the connection is no more open and the next operation causes an error

After returning to .16 version the problem disappeared since the connection remained open.