Intermittent query Result Postgresql

[upswing]

Hi,

I am having some problems with a couple of simple Linq queries.

In each case the same query returns either the expected results or either produces an errot that indicates that the query returned no results and "Error on opening DbConnection"

The errors occur randomly and frequently for a query with identical parameters.

Here is an example of a query:

internal bool checkLogin(string UserName, string Password)
{
try
{
using (TrackerDataContext db = new TrackerDataContext())
{

int RecordsCount = (from c in db.Logins
where ((c.UserName == UserName) && (c.Password == Password))
select c).Count();


if (RecordsCount == 1)
{
return true;
}
}


}
catch (Exception exc)
{
throw new Exception("checkLogin " + exc.Message);
}

return false;
}


The query is not complex and produces the correct response SOMETIMES
Is there a timing issue? Can someone please help..
Thank you

[Shalex]

Please give us the following information:
1) the full call stack of the error with all inner exceptions
2) enable the dbMonitor tool and specify the exact SQL statement which fails to execute

[upswing]

example of a call stack

at Devart.Data.PostgreSql.f.a(Char A_0, Boolean A_1, Boolean A_2)
at Devart.Data.PostgreSql.h.a(Char A_0, Boolean A_1, Boolean A_2)
exception stack

at Devart.Data.PostgreSql.f.a(Boolean A_0, Boolean A_1, Char A_2, Boolean A_3)
at Devart.Data.PostgreSql.f.b(Boolean A_0, Boolean A_1)
at Devart.Data.PostgreSql.f.c(Boolean A_0)
at Devart.Data.PostgreSql.f.a(x A_0, Boolean A_1, Boolean A_2, Boolean A_3)
at Devart.Data.PostgreSql.f.a(x A_0, Boolean A_1, Boolean A_2)
at Devart.Data.PostgreSql.f.a(x A_0, Boolean A_1)
at Devart.Data.PostgreSql.x.af()
at Devart.Data.PostgreSql.u.a(x A_0, Boolean A_1)
at Devart.Data.PostgreSql.u.a(x A_0)
at Devart.Data.PostgreSql.u.c(Boolean A_0)
at Devart.Data.PostgreSql.u.b()
at Devart.Common.DbConnectionInternal.ax()
at Devart.Common.ab.c(DbConnectionBase A_0)
at Devart.Common.DbConnectionClosed.Open(DbConnectionBase outerConnection)
at Devart.Common.DbConnectionBase.Open()
at Devart.Data.PostgreSql.PgSqlConnection.Open()
at Devart.Data.Linq.Engine.cm.g()

[upswing]

further precisions I am using LinqConnect 4.4.529.0 pro edition

[upswing]

I just got the same problem with a different query. There is data in the table and the query sometimes returns the row as expected other times it throws an exception


Here is detailed information

LINQ statement

var UserRecord = (from c in db.Users
where ((c.Login.UserName == UserName) && (c.Login.Password == Password))
select c).First();


Exception Stack trace
"Sequence contains no elements"}

at Devart.Data.Linq.DataProvider.a(c1 A_0, IDbConnection A_1, IDataReader A_2)
at Devart.Data.Linq.DataProvider.a(c1 A_0, Object[] A_1)
at Devart.Data.Linq.DataProvider.c(Expression A_0)
at Devart.Data.Linq.Engine.DataQuery`1.System.Linq.IQueryProvider.Execute[S](Expression expression)
at System.Linq.Queryable.First[TSource](IQueryable`1 source)
at TrackerContext.MyTrackerDataContext.GetMonitoredDevices(String UserName, String Password) in C:\Users\SSI\Documents\Visual Studio 2010\Projects\My.Server\MyServer\PSGSQLDAL.cs:line 488

Log = Console.Out produces this

A first chance exception of type 'Devart.Data.Linq.LinqCommandExecutionException' occurred in Devart.Data.Linq.dll
The thread '<No Name>' (0x11f8) has exited with code 0 (0x0).
SELECT t1."Id", t1."GroupId", t1."LoginId", t1."ContactId", t1."NameId", t1."IsPaying", t1."IsActive", t1."IsConfigured", t1."AccountNumber"
FROM public."Users" t1
INNER JOIN public."Login" t2 ON t1."LoginId" = t2."Id"
WHERE (t2."UserName" = :p0) AND (t2."Password" = :p1) LIMIT 1
-- p0: Input VarChar (Size = 7; DbType = AnsiString) [uname]
-- p1: Input VarChar (Size = 8; DbType = AnsiString) [passwordtest]
-- Context: Devart.Data.PostgreSql.Linq.Provider.PgSqlDataProvider Mapping:

Please tell me if you need additional information

[Shalex]

Code: Select all

var UserRecord = (from c in db.Users
where ((c.Login.UserName == UserName) && (c.Login.Password == Password))
select c).First(); // => "Sequence contains no elements"
if (UserRecords == null){ ...

You should use FirstOrDefault() instead of First().

[upswing]

I use First() because I know or expect the sequence to have at least one element. In other words, when it is an exceptional occurrence when the sequence is empty. Using FirstOrDefault() indicates that it is legal to return an empty element (which is not my case).

If I use FirstOrDefault() as you indicate the exception will not be raised but in doing so I am masking the problem. The real problem is that the query returns "sequence contains no elements" when it should indeed return elements. The row exists in the database so first is ok. If the row does not exists then I want the query to throw an exception so that I can process the exception error.

I used the term intermittent in the post title to indicate that sometimes I get the expected row and other times I get an invalid response (sequence contains no elements)

Could you please explain why sometimes the same query with the same parameters returns the proper response and other times it returns an empty row?. What can cause this knowing that the database is not modified between accesses.. Same table gets same query with same parameters and knowing that I am in debug mode and there is a single connection to the database.

[MariiaI]

Unfortunately, the information you've provided is not enough to reproduce your scenario and determine the reason of the behaviour you have described.
Please specify the following details:
- the connection string being used;
- the DDL/DML scripts of the necessary database tables;
- the definitions of the TrackerDataContext class and entity classes;
- the generated SQL queries;

If possible, please send us a small test project with the corresponding DDL/DML scripts, so that we are able to investigate the issue in more details and find a solution for you.

Looking forward to your reply.