Linq to sql - dynamic order by with IN operator

Question

hello,

i have the following linq query:

using (var db = new MyDataContext())
{
int[] onlineUsers = GetOnline();

var users = (from u in db.Users
             orderby 
(onlineUsers.Contains(u.u_username) && u.u_hasvisible_photo) descending,
 u.u_lastlogin descending
select u.u_username).Skip(startRowIndex).Take(maximumRows).ToList();
}

This works ok and generates a sql query using IN operator.

The problem is that every int from the int[] is passed via a different parameter and i know sql has a limit of 2100 paramters per query.

I think it would be better to use linq Dynamic libary OrderBy to do the sorting.

Instead of "onlineUsers.Contains(u.u_username) && u.u_hasvisible_photo) descending" use .OrderBy(orderquery).

But i couldn't find a way to do it.

any ideas?

Thanks!

Answer 1

If you are using SQL Server 2008, you could create a scalar function which takes a table-valued parameter and then pass the array in as that. It might require special coaxing from the LINQ provider though (you didn't specify which one you were using (Linq-to-Entities or Linq-to-SQL, and I'm assuming SQL Server, even though you just say "sql"), so I'm not sure how composable the query will be.

What you might want to do is create a table valued user-defined function which takes the table-valued parameter (and any other parameters you might have) and expose that through LINQ (you should be able to create something composable from the table-valued function) and then proceed from there.

Answer 2

are you sure this can work with linq? i couldn't get it to work , please help!.

here is my code:

CREATE FUNCTION IsUserOnline
(
 @OnlineUsers As OnlineUsersTableType Readonly,
 @Username as int
)
RETURNS bit
AS
BEGIN
DECLARE @Result as bit

 IF EXISTS (SELECT Username  FROM @OnlineUsers as o WHERE  o.Username = @Username)
  Set @Result = CAST ( 1 AS BIT)
 ELSE
  Set @Result  = CAST( 0 AS BIT)

  RETURN @Result
END

2.

 public ([Parameter(DbType = "OnlineUsersTableType")] int[] onlineUsers, [Parameter(DbType = "int")] int username)
    {
        //return this.CreateMethodCallQuery<bool>(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), onlineUsers).Single();
        return (bool)this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), onlineUsers,username).ReturnValue;
    }
 GO

Problems :

1 . Drag and drop of this function into linq designer doesn't work ("items contains a data type that is not supported by the designer"

2.. i wrote my own wrapper , getting error: " The specified type 'OnlineUsersTableType' is not a valid provider type"

help please... thanks!