This project has moved. For the latest updates, please go here.

Complex queries?

Feb 22, 2013 at 4:45 PM
While OData is increasingly being used for this kind of querying, it is somewhat limited with what it can do. Can Remote Linq handle wildly complex Linq queries?
Coordinator
Feb 25, 2013 at 7:57 AM
OData is a good choice in many scenarios and serves well the purpose of interoperability and abstraction. Remote.Linq however serves a different purpose being a basic library to implement a custom query infrastructure.
Feb 25, 2013 at 3:28 PM
Sorry, but that doesn't really answer my question. Can Remote Linq serialize and deserialize very complex LINQ queries?
Coordinator
Feb 25, 2013 at 4:00 PM
Edited Feb 25, 2013 at 9:16 PM
Ok, I see. However, 'very complex' is non deterministic.

Currently Remote Linq supports any combinatons of:
  • Binary operation expression
  • Unary operation expression
  • Constant value expression
  • Collection of constant values expression
  • Type conversion expression
  • Method call expression
  • Labda parameter expression
  • Property access expression
  • Sorting expression
I think you can do complex queries, yes. More complext than with OData, but it's not realy comparable since it's not on the same level of abstraction.
Hope this helps answering your question. What are you thoughts? Do you have a concrete scenario?
Jun 30, 2014 at 9:06 AM
Regarding more "complex" queries, would be possible to pass more than one parameter to the query ? For instance to implement a "join" between Product and OrderItem (to keep with the example from the sample code). The expression would then refer to a function with several parameters :

System.Linq.Expressions.Expression<Func<Product, OrderItem, bool>> = ....
Coordinator
Jul 1, 2014 at 3:53 PM
That's an interesting question - thanks for posting.

Up to now I didn't see a need to implement this. The reason is that a remote query supporting joins, grouping, etc. basically require projection for the result. Projection implies a non-deterministic result type.
Mainly focusing on WCF it doesn’t seem to make sense allowing dynamic result types - as service contracts, including return types, should be well defined.
Hence, the current main usage of remote.linq is to translate expressions for filtering, sorting, and paging result sets typically queried from a WCF service which accepts an instance of a remote linq query.

To summarize, the aim is to support complex expressions for sorting and filtering. Complex queries like the following were not targeted so far:
        var salesAmountByProductCategory =
            from c in productCategories
            join p in products
                on c.Id equals p.CategoryId
            join i in orderItems
                on p.Id equals i.ProductId
            group new { Category = c, Product = p , OrderItem = i } by c.Name into g
            select new { CategoryName = g.Key, SalesAmount = g.Sum(x => x.OrderItem.Quantity * x.Product.Price) };

Please let me know your thoughts. If someone has ideas on how to execute such a query and return its result from a remote service (e.g. web service), I am very happy to hear them.
Jul 27, 2014 at 8:25 PM
Interlinq Projects supports join's, grouping and selects of anonymous types... so the query should be possible.

I've an active fork of the project at github: https://github.com/jogibear9988/Interlinq-2

At the moment I'm looking to convert the project to WinRT, but I don't know how far I will get!

So at the Moment it's only working in Silverlight and .NET
Coordinator
Jul 28, 2014 at 12:28 PM
Thank you all
Given your posts I’ve taken the challenge beginning this month – since then I’m working on a solution to make such complex queries working end-to-end. I’m not fully there yet – so far only server-side in-memory sources (IEnumerable) work. However, I’ll be able to tell you whether Remote.Linq would also support arbitrary linq providers (IQueriable) in a few days.
I haven’t had a look at InterLINQ yet. I’ll probably do so when I’ll find the time…
Coordinator
Aug 7, 2014 at 6:02 PM
I'm happy to announce a beta version of Remote Linq supporting queries using joins, aggregations, groupings, projections, etc.
Give it a try using the sample application available from the download section. Thanks for your feedback and suggestions.
Oct 23, 2014 at 3:14 PM
Thank you very much for adding the new features. Now it is possible to execute dynamic queries over WCF, it works great!
I only found some problems when using navigation properties (foreign keys) with the Entity Framework generated code (code-first, with the option to generate code from an existing database).
I extended your example with an additional m:n relationship from Products to a new ProductDestinationMarkets table. Because it is a m:n relationship, I added also a ProductDestinationMarkets_Products intermediate "mapping" table.
...
CREATE TABLE [dbo].[ProductDestinationMarkets](
   [Id] [int] NOT NULL,
    [Name] [varchar](50) NOT NULL
)
GO

CREATE TABLE [dbo].[ProductDestinationMarkets_Products] (
    [ProductId] [int] NOT NULL,
    [ProductDestinationMarketId] [int] NOT NULL
);
GO

ALTER TABLE [dbo].[ProductDestinationMarkets]
ADD CONSTRAINT [PK_ProductDestinationMarkets]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

ALTER TABLE [dbo].[ProductDestinationMarkets_Products]
ADD CONSTRAINT [FK_Product]
    FOREIGN KEY ([ProductId])
    REFERENCES [dbo].[Products]([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

ALTER TABLE [dbo].[ProductDestinationMarkets_Products]
ADD CONSTRAINT [FK_ProductDestinationMarket]
    FOREIGN KEY ([ProductDestinationMarketId])
    REFERENCES [dbo].[ProductDestinationMarkets]([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
...
The EF generated code looks (approximately) like this:
    public class Product
    {
        public Product()
        {
            ProductDestinationMarkets = new HashSet<ProductDestinationMarket>();
        }

        public int Id { get; set; }
        public int ProductCategoryId { get; set; }
        public string Name { get; set; }
        public decimal Price { get; set; }

        public virtual ICollection<ProductDestinationMarket> ProductDestinationMarkets { get; set; }
    }

    public class ProductDestinationMarket
    {
        public ProductDestinationMarket()
        {
            Products = new HashSet<Product>();
        }

        public int Id { get; set; }
        public string Name { get; set; }

        public virtual ICollection<Product> Products { get; set; }
    }
...
As you see, the navigation properties are generated as collections in both related classes (the "mapping" table does not appear in code). I guess that these circular references could be a problem for the Remote.Linq expression serializer, as the execution of the sample client joins from the RemoteQueryableToEntityFramework demo project is not possible anymore (a null object exception is thrown).
When serializing such classes with circular references over WCF, the problem can be solved by adding "IsReference=true" to the DataContract, maybe you could solve similarly the problem in Remote.Linq
    [DataContract(IsReference = true)]
    public class Product
...
Oct 23, 2014 at 5:11 PM
I forgot to ask if it would be possible to add to Remote.Linq some functionality that would allow to update or delete multiple records with one call ?
For instance if we have a table with a "name" column (string), to update all records where the name contains "ABC", to new names where "ABC" is replaced by "DEF".
Similary for delete functionality, for instance to remove all records where name starts with "Hello".
Coordinator
Oct 26, 2014 at 8:41 PM
Edited Oct 26, 2014 at 8:58 PM
@RickyTad
Thanks for your questions.

1) Navigation properties not set:
This is a matter of eager vs. lazy loading. You can defined eager loading on server side by adding an include on specific types. Remote linq has no issues in serializing navigation properties. It is happy to serialize full object graphs including circular references.
  public class QueryService
  {
    private Func<EFContext, Type, IQueryable> _provider = (dataStore, type) =>
    {
      if (type == typeof(Product))
      {
        return dataStore.Products.Include(x => x.Markets);
      }

      return dataStore.Set(type);
    };

    public IEnumerable<DynamicObject> ExecuteQuery(Expression queryExpression)
    {
      var efContext = new EFContext();
      return queryExpression.Execute(type => _provider(efContext, type));
    }
  }
2) Update action support
In general linq is meant to query, i.e. read/load/transform data. So you might want to apply a filter expression to an entity set to define which entities to be updated/deleted. However, the specific action, i.e. update/delete, you would probably implement on server side and expose a corresponding service method.


Please let me know if you think I misunderstood...
Oct 27, 2014 at 3:06 PM
Edited Oct 28, 2014 at 11:49 AM
Thank you for the reply, here are some clarifications.
1). Navigation properties.
Here would be nice if the client and not server could decide over the Remote.Linq query if the navigation properties should be loaded or not.
The PoCo classes that I used:
    public class Product
    {
        public Product()
        {
            ProductDestinationMarkets = new HashSet<ProductDestinationMarket>();
        }
        public int Id { get; set; }
        public int ProductCategoryId { get; set; }
        public string Name { get; set; }
        public decimal Price { get; set; }
        public virtual ProductCategory ProductCategory { get; set; }
        public virtual ICollection<ProductDestinationMarket> ProductDestinationMarkets { get; set; }
    }
    public class ProductCategory
    {
        public ProductCategory()
        {
            Products = new HashSet<Product>();
        }
        public int Id { get; set; }
        public string Name { get; set; }
        public virtual ICollection<Product> Products { get; set; }
    }
    public class ProductDestinationMarket
    {
        public ProductDestinationMarket()
        {
            Products = new HashSet<Product>();
        }
        public int Id { get; set; }
        public string Name { get; set; }
        public virtual ICollection<Product> Products { get; set; }
    }
For the different database table relationships that I tested with th EF code-first approach, here is the OnModelCreating() that was used. A "one to many" and a "many to many" relationship should be tested.
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            // Relationships
            // ProductCategory<->Product : one to many
            modelBuilder.Entity<ProductCategory>()
                .HasMany(e => e.Products)
                .WithRequired(e => e.ProductCategory)
                .HasForeignKey(e => e.ProductCategoryId)
                .WillCascadeOnDelete(false);

            // ProductMarket<->Product : many to many
            modelBuilder.Entity<Product>()
                .HasMany(e => e.ProductDestinationMarkets)
                .WithMany(e => e.Products)
                .Map(m => m.ToTable("ProductDestinationMarkets_Products").MapLeftKey("ProductId").MapRightKey("ProductDestinationMarketId"));
        }
I did different tests with different results.
a) All properties and navigation properties should be read -> Test ok:
            var repo = new RemoteRepository(...);
            var simpleQuery =
                from p in repo.Products
                select p;
b) Only selected properties should be read (no navigation properties) -> Test ok:
            var repo = new RemoteRepository(...);
            var simpleQuery =
                from p in repo.Products
                select new { Name = p.Name, Price = p.Price };
c) Only selected properties and the "one to many" navigation property should be read -> Test ok:
            var repo = new RemoteRepository(...);
            var simpleQuery =
                from p in repo.Products
                select new { Name = p.Name, Price = p.Price, Category = p.ProductCategory };
d) Only selected properties and the "many to many" navigation property should be read ->Test failed: the expression serialization throws an exception:
            var repo = new RemoteRepository(...);
            var simpleQuery =
                from p in repo.Products
                select new { Name = p.Name, Price = p.Price, Markets = p.ProductDestinationMarkets };
2) Multiple update and delete operations.
The EntityFramework.Extended library (EntityFramework.Extended) supports batches for update and delete operations. It would be a nice feature if a client could generate such operations by using the Remote.Linq expressions.
I tried to pass the "where" and "update" expressions as parameters to a function, but the "update" expression cannot be serialized to a RemoteLinq expression
           Expression<Func<ProductCategory, bool>> whereExpr = c => c.Name == "Fruit";
           Expression<Func<ProductCategory, ProductCategory>> updExpr = c => new ProductCategory { Name = "Vegetable" };

           Remote.Linq.Expressions.LambdaExpression whereRemoteExpr = whereExpr.ToRemoteLinqExpression();
           Remote.Linq.Expressions.LambdaExpression updRemoteExpr = updExpr.ToRemoteLinqExpression();
Oct 28, 2014 at 12:48 PM
Test d) regarding the navigation properties works fine with the new alpha008 version, thank you for the fix !
Oct 29, 2014 at 3:54 PM
Edited Oct 29, 2014 at 3:56 PM
I took a closer look at the tests involving navigation properties and the lazy loaging behaviour on the server. It seems that through the Remote.Linq serialization/deserialization the "virtual" attributes set in the PoCo entity classes to mark the navigation properties is not available anymore on the server side in the ExecuteQuery() method.
By creating the below query on client side, and executing it on the server side, all the navigation properties are read from database, although lazy loading is active on the server side.
   var simpleQuery =
                from p in repo.Products
                select p;
To get only the entity properties without the navigation properties, the only solution is to work with anonymous types, by selecting each single element to be fetched from the database, like this:
    var simpleQueryAnonymous =
                from p in repo.Products
                select new { Id = p.Id, ProductCategoryId = p.ProductCategoryId, Name = p.Name, Price = p.Price };
      var simpleQueryAnonymousResult = simpleQueryAnonymous.ToList();
Is there any other workaround for getting only the entity properties without the navigation properties, when using "select p" in the query?
Coordinator
Nov 3, 2014 at 7:33 AM
Edited Nov 3, 2014 at 7:34 AM
When working with a DbContext in an n-tier scenario I would strongly suggest to turn-off lazy loading. Otherwise, serialization of the result on server side causes loading all navigation properties lazily in case they have not been loaded eagerly. This means lazy loading in n-tier is typically very bad for performance.

I plan to investigation on providing 'include'-like functions for clients this or next month.

For now you can either not populate navigation properties (turn-off lazy loading and don't set include) or specify them for eager loading (by setting include) as shown in the code snipped posted in this thread on Oct 26 at 10:41 PM.
Nov 13, 2014 at 11:19 AM
Edited Nov 13, 2014 at 5:44 PM
You are right, was my error. Good news regarding the include support.

I have a problem when using the IEnumerable<DynamicObject> ExecuteQuery(Expression query) method and calling on client side the Count() method instead of ToList() to activate the query:
var simpleQuery =
                from p in repo.Products
                select p;
var nrRecords = simpleQuery.Count();
In this case, the Map() method from DynamicObjectMapper.cs
public IEnumerable<object> Map(IEnumerable<DynamicObject> objects, Type type)
{
    if (ReferenceEquals(null, objects))
    {
        throw new ArgumentNullException("objects");
    }

    var items = objects.Select(x => Map(x, type));

    if (ReferenceEquals(null, type))
    {
        return items.ToArray();
    }
    else
    {
        var r1 = MethodInfos.Enumerable.Cast.MakeGenericMethod(type).Invoke(null, new[] { items });
        var r2 = MethodInfos.Enumerable.ToArray.MakeGenericMethod(type).Invoke(null, new[] { r1 });
        return (IEnumerable<object>)r2;
    } 
}
throws an exception when trying to cast (IEnumerable<object>)r2 (exception message something like "The type "System.Int32[]" cannot be converted to type "System.Collections.Generic.IEnumerable`1[System.Object]"")

(the reason is probably related to the problem described here: Why covariance and contravariance do not support value type )

Later Edit:
I "patched" the MapToType() method in DynamicResultMapper.cs and now the primitive (native) types are handled correctly (I declared the IsNativeType() method public to be able to use it also in this code module)
internal static T MapToType<T>(IEnumerable<DynamicObject> dataRecords, IDynamicObjectMapper mapper)
{
    var elementType = TypeHelper.GetElementType(typeof(T));

    if (ReferenceEquals(null, mapper))
    {
        mapper = new DynamicObjectMapper();
    }

    if (DynamicObjectMapper.IsNativeType(typeof(T))) {
        if (typeof(T).IsAssignableFrom(elementType))
        {
            try
            {
                if (dataRecords.Count() != 1)
                {
                    return default(T);                                
                }
                var first = mapper.Map<T>(dataRecords.First());
                return (T)first;
            }
            catch (TargetInvocationException ex)
            {
                throw ex.InnerException;
            }
        }
    }

    var result = mapper.Map(dataRecords, elementType);

    if (ReferenceEquals(null, result))
    {
        return default(T);
    }

    if (typeof(T).IsAssignableFrom(typeof(IEnumerable<>).MakeGenericType(elementType)))
    {
        return (T)result;
    }

    if (typeof(T).IsAssignableFrom(elementType))
    {
        try
        {
            var single = MethodInfos.Enumerable.Single.MakeGenericMethod(elementType).Invoke(null, new object[] { result });
            return (T)single;
        }
        catch (TargetInvocationException ex)
        {
            throw ex.InnerException;
        }
    }

    throw new Exception(string.Format("Failed to cast result of type '{0}' to '{1}'", result.GetType(), typeof(T)));
}
Coordinator
Nov 14, 2014 at 9:05 AM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.