CODEWITHSUNDEEP
c#linqlinq-to-sql
John
John

Reputation: 21

Linq - how can I use a function in a query

I use C# on WP7 (Mango). I try to use a special query because I receive an error:

Method 'Int32 orderBirthday(System.DateTime)' has no supported translation to SQL.

Yes, I know... Linq can't use my function but I don't know the right way...

I have a database table with the columns name and birthday. In my query I will calculate how many days are to the next birthday (from all items) and then I will order with "descending".

static int orderBirthday(DateTime Birthday)
    {
        DateTime today = DateTime.Today;
        DateTime birthday = Birthday;
        DateTime next = new DateTime(today.Year, birthday.Month, birthday.Day);

        if (next < today)
            next = next.AddYears(1);

        int numDays = (next - today).Days;

        // No Conversion
        return numDays;
    }

 public void LoadCollectionsFromDatabase()
    {

        DateTime today = DateTime.Today;

        var toDoItemsInDB = from ToDoItem todo in toDoDB.Items
                            let daysToBirthday = orderBirthday(todo.ItemDate)
                            orderby daysToBirthday ascending
                            select todo;

        // Query the database and load all to-do items.
        AllToDoItems = new ObservableCollection<ToDoItem>(toDoItemsInDB);
.
.
.
}

Upvotes: 0

Views: 623

Answers (4)

Steven
Steven

Reputation: 172646

You either have to pull everything from the database and sort it locally (as Enigmativity) shows, or find a way to express the sort operation in a LINQ statement itself. And since you extracted the sorting behavior into its own function, you probably want to reuse this logic. In that case your best bet is to create a function that filters an IQueryable.

Here is an example of how to do this:

public static IOrderedQueryable<Item> OrderByBirthday(
    this IQueryable<Item> items)
{
    return
        from item in items
        let today = DateTime.Today
        let birthday = item.ItemDate
        let next = new DateTime(today.Year, birthday.Month, birthday.Day)
        let next2 = next < today ? next.AddYears(1) : next
        orderby (next - today).Days
        select item;
}

You can use the method as follows:

var toDoItemsInDB = OrderByBirthday(toDoDB.Items);

Or you can use it as an extension method:

var toDoItemsInDB = toDoDB.Items.OrderByBirthday();

Upvotes: 1

Massimo Zerbini
Massimo Zerbini

Reputation: 3191

If you don't want to load all the items in memory and you want the database execute the calculation, you can write a stored procedure that can execute complex calculation and call the procedure using ADO or EF.

Upvotes: 0

Jon Hanna
Jon Hanna

Reputation: 113242

Two ways:

One: Pull it from Linq2SQL to Linq2Objects using ToEnumerable(), and then use orderBirthday at the C# level.

Advantage is that it's simple to code and maintain, disadvantage is that it can be less efficient (depends on just what you are doing.

Two: Write an equivalent function in SQL, let's say it was called dbo.orderBirthday. Make your orderBirthday method a non-static method of your datacontext-derived class, and then mark your method as having a SQL function equivalent:

[Function(Name="dbo.orderBirthday",IsComposable=true)] //IsComposable is true for functions that can be used within queries, false for stored procedures that must be called on their own.
public int OrderBirthday([Parameter(Name="@birthday",DbType="datetime") DateTime birthday)
{
    return Helper.OrderBirthday(birthday); // just to show that we can keep the static version around if we want and call into it. Alternatively we could just move the whole body here.
}

Here the C# code is used in a non-Linq2SQL context, and the SQL code is used in composing a SQL query in a Linq2SQL context.

Advantage: Can stay within SQL longer. Disadvantage: Two versions of the same method can fall out of sync and cause bugs.

It's also possible to have the C# code call the SQL code all the time:

[Function(Name="dbo.orderBirthday",IsComposable=true)]
public int OrderBirthday([Parameter(Name="@birthday",DbType="datetime") DateTime birthday)
{
    return (int)ExecuteMethodCall(this, (MethodInfo)MethodInfo.GetCurrentMethod(), birthday).ReturnValue;
}

Advantage: Keeps one version (the SQL) as the only version, so it can't fall out of synch with the C# version. Disadvantage: Calls SQL even when working on objects that have nothing to do with SQL.

Upvotes: 0

Enigmativity
Enigmativity

Reputation: 117054

It's easy if you do this:

    var toDoItemsInDB = from ToDoItem todo in toDoDB.Items.ToArray()
                        let daysToBirthday = orderBirthday(todo.ItemDate)
                        orderby daysToBirthday ascending
                        select todo.;

Notice the .ToArray() added to Items. You basically bring the results into memory and them your function can work.

Upvotes: 0

Related Questions