convert query to DLinq

[Andrus]

I have query like

SELECT *
FROM customers
WHERE location<>'GB'
ORDER BY CustomerName, ID;

I need to find row index in this query which contains known customer (
id=123, name= "Airbus") without reading lot of data from server.

In sql I can use

SELECT COUNT(*)
FROM customers
WHERE location<>'GB' and (CustomerName, ID) <= ('Airbus', 123);


How to convert this query to linq or is there better way ?

Andrus.

 

[Michel Walsh]

The data is probably already local (unless the query is still deferred).

You use Oracle? the vectorial-syntax is not supported in MS SQL Server 2005,
and I doubt it is supported in LINQ, so you should expand the criteria to:


WHERE location <> 'GB' and (CustomerName <= 'Airbus' OR (CustomerName =
'Airbus' AND id= 123));


which becomes a 'standard' where clause.


Alternatively, you can loop until you find the desired record, or try to use
the method Select( (p, i) => i )
where i is the index position of the record, supplied for you, by the system
(though I personally failed to get something useful from it, for THIS case).

The proposed Select comes from the prototype: IEnumerable<S, T> Select<T,
S>(this IEnumerable<T> source, Func<T, int, S> selector);



Vanderghast, Access MVP

 

[Andrus]

and I doubt it is supported in LINQ, so you should expand the criteria to:

WHERE location <> 'GB' and (CustomerName <= 'Airbus' OR (CustomerName =
'Airbus' AND id= 123));

For multiple customers with name Airbus this causes incorrect result.
Did you mean AND id<= 123 ? :

WHERE location <> 'GB' and (CustomerName <= 'Airbus' OR (CustomerName =
'Airbus' AND id<= 123));

Sometimes I have 4-5 order keys.
Should I create query condition manually for all cases or is it possible to
create some method which creates new query expression tree automatically
from original query Where and Order By expression trees ?

Alternatively, you can loop until you find the desired record, or try to
use the method Select( (p, i) => i )
where i is the index position of the record, supplied for you, by the
system (though I personally failed to get something useful from it, for
THIS case).

How I can get position of the record from system ?
Sql served do not have such function as I know.

I need to show this record in WinForms DataGridView using local mode.
The alternative way should be to force virtualmode DataGridView to show
neighbour records by given id. However I have no idea is this possible or
not.


Andrus.

 

[Michel Walsh]

I meant id <= 123, indeed.

If you have different conditions, and if you can write queries, I suggest to
use an ad hoc query rather than using a more general query which cpuld be
poorly optimized. Sure, making a specific query each time implies that the
query plan will have to be evaluated, but that is generally very fast.

The Select( (p,i) => ... ) syntax is from LINQ to object:


var myData=new []{new {id=11, who="hello"}, new{ id=17,
who="oz"} , new {id=22, who="bye"}};
var toto = myData.Select((p, i) => new { p.id, p.who, position
=i });
var jojo = toto.Where(q => q.who == "bye").Select(z =>
z.position);
int k = jojo.ToList()[0];
System.Diagnostics.Debug.Assert(k == 2);


Note that you can append where clause. Here, I added a WHERE clause BETWEEN
two SELECT clauses and while I did it in two steps, it could have been done
in just one:


int kk = myData.Select((p, i) => new { p.id, p.who, position =
i })
.Where(q => q.who == "bye").Where(q => q.id == 22).Select(z =>
z.position)
.ToList()[0];



where I even add a second WHERE clause to show the concept of building
successively additional conditions.




Vanderghast, Access MVP

 

[Michel Walsh]

Just an important note. If you try:

int kk = myData
.Where(q => q.who == "bye")
.Where(q => q.id == 22)
.Select((z, i) => i)
.ToList()[0];

you end up with who="bye", id=22, as in position 0. Which is not what we
want. That is why we need the first Select, to un-deferred, to 'instantiate
( I borrow the expression, it is not an official one) the 'position':



int kk = myData
.Select((p, i) => new { p.id, p.who, position = i })
.Where(q => q.who == "bye")
.Where(q => q.id == 22)
.Select((z, i) => i)
.ToList()[0];



Still somehow un-usual to have a Where clause between two Select clauses.



Vanderghast, Access MVP

I meant id <= 123, indeed.

If you have different conditions, and if you can write queries, I suggest
to use an ad hoc query rather than using a more general query which cpuld
be poorly optimized. Sure, making a specific query each time implies that
the query plan will have to be evaluated, but that is generally very fast.

The Select( (p,i) => ... ) syntax is from LINQ to object:


var myData=new []{new {id=11, who="hello"}, new{ id=17,
who="oz"} , new {id=22, who="bye"}};
var toto = myData.Select((p, i) => new { p.id, p.who, position
=i });
var jojo = toto.Where(q => q.who == "bye").Select(z =>
z.position);
int k = jojo.ToList()[0];
System.Diagnostics.Debug.Assert(k == 2);


Note that you can append where clause. Here, I added a WHERE clause
BETWEEN two SELECT clauses and while I did it in two steps, it could have
been done in just one:


int kk = myData.Select((p, i) => new { p.id, p.who, position =
i })
.Where(q => q.who == "bye").Where(q => q.id == 22).Select(z
=> z.position)
.ToList()[0];



where I even add a second WHERE clause to show the concept of building
successively additional conditions.




Vanderghast, Access MVP

For multiple customers with name Airbus this causes incorrect result.
Did you mean AND id<= 123 ? :

WHERE location <> 'GB' and (CustomerName <= 'Airbus' OR (CustomerName =
'Airbus' AND id<= 123));

Sometimes I have 4-5 order keys.
Should I create query condition manually for all cases or is it possible
to create some method which creates new query expression tree
automatically
from original query Where and Order By expression trees ?


How I can get position of the record from system ?
Sql served do not have such function as I know.

I need to show this record in WinForms DataGridView using local mode.
The alternative way should be to force virtualmode DataGridView to show
neighbour records by given id. However I have no idea is this possible or
not.


Andrus.

 

[Michel Walsh]

.... argh... late edition. The last bit of code should be:


int kk = myData
.Select((p, i) => new { p.id, p.who, position = i })
.Where(q => q.who == "bye")
.Where(q => q.id == 22)
.Select((z) => z.position)
.ToList()[0];

 

[Andrus]

Thank you.

I need to retrieve specific row number from possibly large server table in
database.
Your sample requires to read rows from server over slow internet connection.
So it seems that I cannot use it. Is it so ?

I created extension method below to generate dynamic string comparison SQL
using
Microsoft.VisualBasic.CompilerServices.Operators.CompareString()

which creates

Where( c=> Operators.CompareString( c.CustomerId, "AIRBU" ) < 1 )

public static IQueryable<T> LessThanOrEqual<T>(this IQueryable<T>
source, string property, object value)
{
ParameterExpression obj = Expression.Parameter(typeof(T), "x");
Expression val = Expression.Constant(value, typeof(string));
Expression prop = Expression.Property(obj, property);
Expression call = Expression.Call(
typeof(Microsoft.VisualBasic.CompilerServices.Operators).GetMethod("CompareString",
new[] { typeof(string), typeof(string), typeof(bool) }), prop,
val,
Expression.Constant(false, typeof(bool)));
BinaryExpression testExp = LambdaExpression.LessThan(call,
Expression.Constant(1, typeof(int))
);
return source.Where(Expression.Lambda<Func<T, bool>>(testExp,
obj));
}

Is this best solution ?

Andrus.

I meant id <= 123, indeed.

If you have different conditions, and if you can write queries, I suggest
to use an ad hoc query rather than using a more general query which cpuld
be poorly optimized. Sure, making a specific query each time implies that
the query plan will have to be evaluated, but that is generally very fast.

The Select( (p,i) => ... ) syntax is from LINQ to object:


var myData=new []{new {id=11, who="hello"}, new{ id=17,
who="oz"} , new {id=22, who="bye"}};
var toto = myData.Select((p, i) => new { p.id, p.who, position
=i });
var jojo = toto.Where(q => q.who == "bye").Select(z =>
z.position);
int k = jojo.ToList()[0];
System.Diagnostics.Debug.Assert(k == 2);


Note that you can append where clause. Here, I added a WHERE clause
BETWEEN two SELECT clauses and while I did it in two steps, it could have
been done in just one:


int kk = myData.Select((p, i) => new { p.id, p.who, position =
i })
.Where(q => q.who == "bye").Where(q => q.id == 22).Select(z
=> z.position)
.ToList()[0];



where I even add a second WHERE clause to show the concept of building
successively additional conditions.




Vanderghast, Access MVP

For multiple customers with name Airbus this causes incorrect result.
Did you mean AND id<= 123 ? :

WHERE location <> 'GB' and (CustomerName <= 'Airbus' OR (CustomerName =
'Airbus' AND id<= 123));

Sometimes I have 4-5 order keys.
Should I create query condition manually for all cases or is it possible
to create some method which creates new query expression tree
automatically
from original query Where and Order By expression trees ?


How I can get position of the record from system ?
Sql served do not have such function as I know.

I need to show this record in WinForms DataGridView using local mode.
The alternative way should be to force virtualmode DataGridView to show
neighbour records by given id. However I have no idea is this possible or
not.


Andrus.

 

[Michel Walsh]

I don't qualify myself as an expert about how LINQ to SQL really work in all
cases, but if your goal is to minimize the exchange between the server and
the client, andyou need a special function in the criteria, I won't rate
very high a solution involving the client code being called for each rows of
the table. Part of the problem is that not each and every possible C#
reachable methods are also known by MS SQL Server (which is obvious, I you
think that C# is more general than SQL), so the question will be: is LINQ to
SQL is a) able to grab the abstraction and b) would MS SQL Server will be
able to translate it. And that works both way. As example, is LINQ to SQL
aware of MS SQL Server 2005 Rank operator? I doubt it. So, but back to the
question here, is MS SQL Server would understand
IQueryable<T>LessThanOrEqual<T>(...) ? I seriously doubt it would.

A possible better solution could be to write C# code that will be executed
on the Server (not at the client side) using User-Defined Functions, in MS
SQL Server, and using C# to define those functions. That does not involve
LINQ. It just the possibility to define user functions in C# rather than in
T-SQL, but you do it on the server (I assume you have the right to do so,
with security clearance). You can see the MS SQL Server doc for more details
(like
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/denet9/html/20dcf802-c27d-4722-9cd3-206b1e77bee0.htm)

Is that makes sense? Or maybe I don't understand the whole problem?


Vanderghast, Access MVP

Thank you.

I need to retrieve specific row number from possibly large server table in
database.
Your sample requires to read rows from server over slow internet
connection.
So it seems that I cannot use it. Is it so ?

I created extension method below to generate dynamic string comparison SQL
using
Microsoft.VisualBasic.CompilerServices.Operators.CompareString()

which creates

Where( c=> Operators.CompareString( c.CustomerId, "AIRBU" ) < 1 )

public static IQueryable<T> LessThanOrEqual<T>(this IQueryable<T>
source, string property, object value)
{
ParameterExpression obj = Expression.Parameter(typeof(T), "x");
Expression val = Expression.Constant(value, typeof(string));
Expression prop = Expression.Property(obj, property);
Expression call = Expression.Call(
typeof(Microsoft.VisualBasic.CompilerServices.Operators).GetMethod("CompareString",
new[] { typeof(string), typeof(string), typeof(bool) }),
prop, val,
Expression.Constant(false, typeof(bool)));
BinaryExpression testExp = LambdaExpression.LessThan(call,
Expression.Constant(1, typeof(int))
);
return source.Where(Expression.Lambda<Func<T, bool>>(testExp,
obj));
}

Is this best solution ?

Andrus.

I meant id <= 123, indeed.

If you have different conditions, and if you can write queries, I suggest
to use an ad hoc query rather than using a more general query which cpuld
be poorly optimized. Sure, making a specific query each time implies that
the query plan will have to be evaluated, but that is generally very
fast.

The Select( (p,i) => ... ) syntax is from LINQ to object:


var myData=new []{new {id=11, who="hello"}, new{ id=17,
who="oz"} , new {id=22, who="bye"}};
var toto = myData.Select((p, i) => new { p.id, p.who, position
=i });
var jojo = toto.Where(q => q.who == "bye").Select(z =>
z.position);
int k = jojo.ToList()[0];
System.Diagnostics.Debug.Assert(k == 2);


Note that you can append where clause. Here, I added a WHERE clause
BETWEEN two SELECT clauses and while I did it in two steps, it could have
been done in just one:


int kk = myData.Select((p, i) => new { p.id, p.who, position =
i })
.Where(q => q.who == "bye").Where(q => q.id == 22).Select(z
=> z.position)
.ToList()[0];



where I even add a second WHERE clause to show the concept of building
successively additional conditions.




Vanderghast, Access MVP

and I doubt it is supported in LINQ, so you should expand the criteria
to:


WHERE location <> 'GB' and (CustomerName <= 'Airbus' OR (CustomerName =
'Airbus' AND id= 123));

For multiple customers with name Airbus this causes incorrect result.
Did you mean AND id<= 123 ? :

WHERE location <> 'GB' and (CustomerName <= 'Airbus' OR (CustomerName =
'Airbus' AND id<= 123));

Sometimes I have 4-5 order keys.
Should I create query condition manually for all cases or is it possible
to create some method which creates new query expression tree
automatically
from original query Where and Order By expression trees ?

Alternatively, you can loop until you find the desired record, or try
to
use the method Select( (p, i) => i )
where i is the index position of the record, supplied for you, by the
system (though I personally failed to get something useful from it, for
THIS case).

How I can get position of the record from system ?
Sql served do not have such function as I know.

I need to show this record in WinForms DataGridView using local mode.
The alternative way should be to force virtualmode DataGridView to show
neighbour records by given id. However I have no idea is this possible
or
not.


Andrus.

 

[Michel Walsh]

That is not C# neither Net related, but MS SQL Server 2005 has a
Row_Number() predefined function:

----------------------
SELECT *
FROM ( SELECT Row_Number() OVER (ORDER BY zip DESC) as rowNum,
au_lname, au_fname
FROM authors) AS x
WHERE rowNum BETWEEN 4 AND 6
-----------------------


I doubt LINQ can reaches it, so you may have to write an ad hoc query (or a
stored proc, if the where clause is always in a simple pattern)



Vanderghast, Access MVP

Thank you.

I need to retrieve specific row number from possibly large server table in
database.

(...)

 

[Andrus]

User can click in any row in dataGridView and place any condition. My
backend is PostgreSQL which does not have row number function.
PostgreSQL has native tuple comparison operators, it allows (c1,c2) <
(v1,v2) in queries.
This requires to use string concatenation to build SQL queries directly, I
cannot use Linq then.

So I think I must generate tuple comparison (c1,c2) < (v1,v2) by
dynamically creating expression tree

.... and c1<=v1 and ( c1<v2 or c2<=v2 )

as I described in other thread.
Issue is that sometimes I need to compare up to 6 properties

(c1,c2, ..., c6) < (v1,v2, .. ,v6)

I have no idea how to create such expression tree.

Andrus.

 

[Michel Walsh]

I would suggest to use a command. Here, targeting MS SQL Server:

....
using System.Data;
using System.Data.SqlClient;

....

using (SqlConnection xnn = new SqlConnection(ConnexionString))
{
using (SqlCommand sql = xnn.CreateCommand())
{
sql.CommandType = CommandType.Text;
xnn.Open();


// get a scalar, such as the max of something
// You can build the string at run time.

sql.CommandText = "SELECT MAX(...) FROM ...";


int abc = (int)sql.ExecuteScalar(); // read the result,
a single scalar


// use a data reader to get multiple rows
// on some string you can build at run time

sql.CommandText = "SELECT ... ";

using (SqlDataReader r = sql.ExecuteReader())
{
while (r.Read())
{
... // capture the row
}
} // using sql data reader

} // using sql command

} // using sql connexion




I don't know if ADO.Net could handle directly the tuple-comparison,
targeting PostgreSQL, or if you will have to expand dynamically the
comparison at run time, but you can definitively built a string, at run
time, and used it as CommandText.



Vanderghast, Access MVP

 

[Michel Walsh]

In the same spirit, but more LINQ related, you can also use ExecuteQuery:


var query = dataContext.ExecuteQuery<className>( @"SELECT ... WHERE ...
AND... OR... ");


where className is appropriate to recuperate the result of your dynamically
built SQL statement, as a string.




Vanderghast, Access MVP

 

[Andrus]

Application creates business part of query dynamically using commands like

if (!string.IsNullOrEmpty( Param.CustName ) )
q = q.Where( c=> c.CustomerName==Param.CustName );

if (!string.IsNullOrEmpty( Param.CustCity ) )
q = q.Where( c=> c.City==Param.CustCity );

I do'nt know a way to get generated sql as string.
So this approach requires to use string builder instead of this. Large parts
of application should use string concatenation to build business-logic
queries instead of DLinq.

I'm not sure is this good solution. So I'm searching for a way to create
extension method

IQueryable<T> LessThanOrEqual<T>( IQueryable<T> this, string c1, string c2,
object v1, object2, Type v1Type, Type v2Type )

or in general form

IQueryable<T> LessThanOrEqual<T>( IQueryable<T> this, string[] propertyName,
object[] propertyValue, Type[] propertyValueType)

which generates comparison query (c1,c2) <= (v1,v2)

I created single property extension method below but don't know how to
change it to use two properties.

Andrus.

// creates property <= value query.
public static IQueryable<T> LessThanOrEqual<T>(this IQueryable<T> source,
string property, object value, Type propertyType)
{
ParameterExpression param = Expression.Parameter(typeof(T),
"x");
Expression val;
val = Expression.Constant(value, propertyType);
Expression prop = Expression.Property(param, property);
BinaryExpression testExp = null;
if (propertyType == typeof(string))
{
Expression call = Expression.Call(
typeof(Microsoft.VisualBasic.CompilerServices.Operators).GetMethod("CompareString",
new[] { typeof(string), typeof(string), typeof(bool) }),
prop, val,
Expression.Constant(false, typeof(bool)));
testExp = LambdaExpression.LessThan(call,
Expression.Constant(1, typeof(int)));
}
else
{
testExp = LambdaExpression.LessThanOrEqual(prop, val);
}
return source.Where(Expression.Lambda<Func<T, bool>>(testExp,
param));
}