LINQ to SQL - question on select...

[Guest]

Is it possible to call another method out of the select statement?
In this example I am trying to call GetItem() from within the first select
statement.
But I get the following error"
"Method 'GetItem(MyDataContext, System.Guid, Boolean)' has no supported
translation to SQL."

public static List<Item> GetProducts(Guid customerID)
{
List<Item> services = new List<Item>();
using (MyDataContext dc = new MyDataContext(connectionString))
{
var q = from p in dc.Products
select GetItem(dc, p.ServiceID, Convert.ToBoolean(p.IsKit));
return q.ToList();
}
}

private static Item GetItem(ProductServiceDataContext dc, Guid serviceID,
bool isKit)
{
if (isKit)
{
var q = from p in dc.Products
where p.ServiceID == serviceID
select new Service(...) // Service is a derivative of Item
return q.First();
);
...
}

This seems like it should be legal.

 

[Nicholas Paldino [.NET/C# MVP]]

It can't be legal. If this was LINQ to Objects, then it would work, but
you are accessing the SQL Server, and the query in code is being transformed
into a SQL statement which is then executed on the server.

The thing is, LINQ to SQL doesn't know how to transform your GetItem
method into SQL.

You might be better off creating a stored procedure which will give you
the results you want.

Also, you are getting the first item for the products, but not
indicating any order. The order of the items returned will be how they are
stored on disk for the table, unless you are using a clustered index of some
kind, this order is undefined (or at the best, not well defined). You
should be ordering the result set before you take the first item.

 

[Jon Skeet [C# MVP]]

Is it possible to call another method out of the select statement?
In this example I am trying to call GetItem() from within the first select
statement.
But I get the following error"
"Method 'GetItem(MyDataContext, System.Guid, Boolean)' has no supported
translation to SQL."

Indeed. Out of interest, how did you expect it to handle this?

What you *can* do is force it to load the items into memory using
AsEnumerable() like this:

using (MyDataContext dc = new MyDataContext(connectionString))
{
var q = from p in dc.Products.AsEnumerable()
select GetItem(dc, p.ServiceID, Convert.ToBoolean(p.IsKit));
return q.ToList();
}

It would end up doing a query for each item, however, which isn't a
good plan.

Is all the information you need in the Products table, and are there
sufficiently few items that you could load them all into memory? If so,
I'd just do that way - in memory.