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.

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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>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.

> 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.

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




"Andrus" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>> 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.

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


"Michel Walsh" <(E-Mail Removed)> wrote in message
news:OSBmf$(E-Mail Removed)...
>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
>
>
>
>
> "Andrus" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>> 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.
>

.... 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];



"Michel Walsh" <(E-Mail Removed)> wrote in message
news:0AAC6558-65B7-4A9A-B3FC-(E-Mail Removed)...
> 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
>

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.

"Michel Walsh" <(E-Mail Removed)> wrote in message
news:OSBmf$(E-Mail Removed)...
>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
>
>
>
>
> "Andrus" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>> 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.
>

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


"Andrus" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>
> "Michel Walsh" <(E-Mail Removed)> wrote in message
> news:OSBmf$(E-Mail Removed)...
>>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
>>
>>
>>
>>
>> "Andrus" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>>> 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.
>>
>

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


"Andrus" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Thank you.
>
> I need to retrieve specific row number from possibly large server table in
> database.
(...)

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" <(E-Mail Removed)> wrote in message
news:3DDE2DEE-2889-4CA5-B4FF-(E-Mail Removed)...
> 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
>
>
> "Andrus" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Thank you.
>>
>> I need to retrieve specific row number from possibly large server table
>> in database.
> (...)
>
>