Local and Remote Method Calls

In This Topic

LinqConnect Documentation
Local and Remote Method Calls
[email protected]

A lot of common scenarios suppose using methods in your LINQ queries. For example, if you search for a company by a part of its name, you can do it in the following way:

C#csharp	Copy Code
var query = from company in context.Companies where company.CompanyName.Contains("Borland") select company; var borlandDivisions = query.ToList();

Visual Basic	Copy Code
Dim query = From company In context.Companies _ Where company.CompanyName.Contains("Borland") _ Select company Dim borlandDivisions = query.ToList()

Note the '.Contains("Borland")' call. If such a method has an adequate representation in SQL, LinqConnect translates it to the corresponding dialect (e.g., .Contains() is translated into the 'LIKE' clause in the above sample). Method calls being translated into SQL and executed at the server are called remote ones. Such are, for example, the most common methods of string and IEnumerable, and many of math and convertion methods.

The method calls that cannot be translated into SQL and are executed in CLR are called local. Such are, e.g., all your custom methods. For instance, let us use a custom method to get the price list for the Christmas sale (the sample is based on the CRM Demo database):

C#csharp	Copy Code
public static double XMasDiscountedPrice(double? price) { return price.GetValueOrDefault() / 2; } static void Main(string[] args) { CrmDemoDataContext context = new CrmDemoDataContext() { Log = Console.Out }; var query = from product in context.Products where product.Discontinued.GetValueOrDefault() == true select new { product.ProductName, Price = XMasDiscountedPrice(product.Price) }; var xmasSale = query.ToList(); }

C#csharp

Copy Code

public static double XMasDiscountedPrice(double? price) {
  return price.GetValueOrDefault() / 2;
}
 
static void Main(string[] args) {  
  CrmDemoDataContext context = new CrmDemoDataContext() { Log = Console.Out };
  var query = from product in context.Products
              where product.Discontinued.GetValueOrDefault() == true
              select new {
                product.ProductName,
                Price = XMasDiscountedPrice(product.Price) };
  var xmasSale = query.ToList();
}

Visual Basic	Copy Code
Public Shared Function XMasDiscountedPrice(price As System.Nullable(Of Double)) As Double Return price.GetValueOrDefault() / 2 End Function Private Shared Sub Main(args As String()) Dim context As New CrmDemoDataContext() With {.Log = Console.Out} Dim query = From product In context.Products _ Where product.Discontinued.GetValueOrDefault() = True _ Select New With {product.ProductName, .Price = XMasDiscountedPrice(product.Price)} Dim xmasSale = query.ToList() End Sub

Visual Basic

Copy Code

Public Shared Function XMasDiscountedPrice(price As System.Nullable(Of Double)) As Double
    Return price.GetValueOrDefault() / 2
End Function
 
Private Shared Sub Main(args As String())
    Dim context As New CrmDemoDataContext() With {.Log = Console.Out}
 
    Dim query = From product In context.Products _
                Where product.Discontinued.GetValueOrDefault() = True _
                Select New With {product.ProductName, .Price = XMasDiscountedPrice(product.Price)}
    Dim xmasSale = query.ToList()
End Sub

As you can notice, the Price column is mentioned in the generated SQL on its own, without any additional methods:

SELECT t1."ProductName", t1."Price" AS C1
FROM CRM_DEMO."Products" t1
WHERE (COALESCE(t1."Discontinued",0)) = :p0

This is because LinqConnect

parsed the above LINQ expression;
determined that the XMasDiscountedPrice cannot be translated and therefore should be executed locally;
found that the method takes the Product.Price property as a parameter, thus it is necessary to fetch the corresponding column.

Note:
Local method calls are allowed only in the selection part of LINQ queries; otherwise, you get InvalidOperationException. The reason is that if local method calls are used in a restrictive clause like WHERE, then filtering has to be performed at the client side. This means that you fetch the whole result set and enumerate through it verifying your condition for each element; i.e., the query to the server is done as if there were no such condition. In our opinion, it is more transparent and less error-prone to do this explicitly: first fetch the 'large' result set, and after that apply all limitations based on custom methods.

Note:

Local method calls are allowed only in the selection part of LINQ queries; otherwise, you get InvalidOperationException. The reason is that if local method calls are used in a restrictive clause like WHERE, then filtering has to be performed at the client side. This means that you fetch the whole result set and enumerate through it verifying your condition for each element; i.e., the query to the server is done as if there were no such condition. In our opinion, it is more transparent and less error-prone to do this explicitly: first fetch the 'large' result set, and after that apply all limitations based on custom methods.

[email protected]