Using Iqueryable with Linq

Using IQueryable with Linq

Marc Gravell's answer is very complete, but I thought I'd add something about this from the user's point of view, as well...

The main difference, from a user's perspective, is that, when you use IQueryable<T> (with a provider that supports things correctly), you can save a lot of resources.

For example, if you're working against a remote database, with many ORM systems, you have the option of fetching data from a table in two ways, one which returns IEnumerable<T>, and one which returns an IQueryable<T>. Say, for example, you have a Products table, and you want to get all of the products whose cost is >$25.

If you do:

 IEnumerable<Product> products = myORM.GetProducts();
 var productsOver25 = products.Where(p => p.Cost >= 25.00);

What happens here, is the database loads all of the products, and passes them across the wire to your program. Your program then filters the data. In essence, the database does a SELECT * FROM Products, and returns EVERY product to you.

With the right IQueryable<T> provider, on the other hand, you can do:

 IQueryable<Product> products = myORM.GetQueryableProducts();
 var productsOver25 = products.Where(p => p.Cost >= 25.00);

The code looks the same, but the difference here is that the SQL executed will be SELECT * FROM Products WHERE Cost >= 25.

From your POV as a developer, this looks the same. However, from a performance standpoint, you may only return 2 records across the network instead of 20,000....

How to get IQueryable() instead of List() using LinQ?

I suggest you look into join. It is basically what you are trying to do

var result = (from event in DBContext.Events
              where event.EventName.Contains(searchParam)
              join journal in DBContext.Journals on event.EventID equals journal.JournalID
              select new JournalGrouped()
              {
                JournalID = journal.JournalID,
                JournalDate = journal.JournalDate
              }).Distinct().ToList();

Or better still since you are using Entity Framework use navigation properties and let the framework do the work for you. After defining the navigation properties it will look something like:

from journal in DBContext.Journals.Include("full name of your Event class")
where journal.Event.EventName.Contains(searchParam)
select new JournalGrouped()
{
   JournalID = journal.JournalID,
   JournalDate = journal.JournalDate
}

If you for some reason what to stick with the way you currenly are doing it then:

var values = DBContext.Events.Where(o => o.EventName.Contains(searchParam))
                             .Select(x => x.EventID);

var GroupedList = DBContext.Journals.Where(j => values.Contain(j.eventID))
                                    .Select(t => new JournalGrouped()
                                    {
                                        JournalID = t.JournalID,
                                        JournalDate = t.JournalDate
                                    }).Distinct();

C# - IQueryable query how to select?

To select specific columns you need to project to an object with those properties (anonymous or custom)

.Select(t => new { t.FirstName, t.Name, t.NameCode })

In addition you can put the two where conditions in the same predicate:

entities.ImportNameValidation
        .Where(y => y.FirstName == searchedName && y.NameType == comboValue)
        .Select(t => new { t.FirstName, t.Name, t.NameCode })

Or in query syntax:

from item in entities.ImportNameValidation
where item.FirstName == searchedName && item.NameType == comboValue   
select new { item.FirstName, item.Name, item.NameCode }

As the items in the collections are no longer of type ImportNameValidation you cannot assign this to a List<ImportNameValidation>. To do so project to a custom DTO object that contains there 3 properties (you cannot project to the mapped type - will cause an error):

List<ImportNameValidationDTO> result = entities.ImportNameValidation
    .Where(y => y.FirstName == searchedName && y.NameType == comboValue)
    .Select(t => new ImportNameValidationDTO { t.FirstName, t.Name, t.NameCode })
    .ToList();

Using an IQueryable in another IQueryable

Problem is not IQueryable inside IQueryable, because you can include subqueries just not the way you did.

In your example whole Select is represented as expression tree. In that expression tree there is something like :

CALL method DBEntitiesCompanyExtensions.GetCompanyProducts

Now EF should somehow traslate this into SQL SELECT statement. It cannot do that, because it cannot "look inside" GetCompanyProducts method and see what is going on there. Nor can it execute this method and do anything with it's result. The fact it returns IQueryable does not help and is not related.

Problems with converting List to IQueryable in Linq

Why should I often must make ToList() conversion for avoiding errors?

I often think about Linq queries in three "levels":

IQueryable - there are designed to translate a Linq query into an equivalent database (or whatever data source you're using) query. Many Linq and non-Linq operations just can't be translated into its SQL or other equivalent, so this layer would throw an error. Even operations that seem simple (like splitting a string) are difficult if not impossible to do in SQL
IEnumerable - in this layer, Linq queries are done in memory, so there's much more flexibility to do custom operations. To get from the IQueryable layer to the IEnumerable layer, the AsEnumerable() call is the most straightforward. That separates the part of the query that gets raw data from the part that can create custom objects, do more complex filtering and aggregations, etc. Note that IEnumerable still uses "deferred execution", meaning that at this stage, the query is just a query - the results don;t actually get computed until you enumerate it, either with a foreach loop or by advancing to the next layer:
List/Array/etc. This is where queries are executed and turned into concrete collections. Some of the benefits of this layer are serializability (you can't "serialize" an enumerator) and eager-loading (as opposed to deferred execution described above).

So you're probably getting an error because you have some part of your query that can't be translated by the underlying Queryable provider, and using ToList is a convenient way to materialize the raw data into a list, which allows you to do more complex operations. Noe that AsEnumerable() would do the same thing but would maintain deferred execution.

Is this practice good?

It can be, but you might easily be getting more data than you need by doing filtering at the list level rather than at the database level. My general practice is to get as much of the query done at the database level, and only moving to the enumrerable/list level when there's no known way to translate the rest of the query to SQL.

Is the speed of my solution worse than IQueryable-based approach?

The only way to know is to try it both ways and measure the difference. But it's a pretty safe bet that if you get more raw data than you need and filter in memory that you'll have worse performance.

How to use Func in a linq query that provide an IQueryable output

This is really down to the leaky abstraction exposed by IQueryable<> in combination with ORMs.

The first attempt will infact work when executing in memory; however, it's not the case when utilizing an ORM. The reason your first code won't work with LINQ to entities, is that a Func<> is compiled code. It doesn't represent an expression tree which can be easily converted to SQL.

The second attempt is the natural attempted solution, but breaks because of the somewhat magical conversion of your code into an expression tree. While you're writing the select, you're not coding against Expression objects. But when you compile the code; C# will automatically convert it into an expression tree. Unfortunately, there's no way to easily bring actual Expression items into the mix.

What you need is:

A placeholder function to grab a reference to your expression
An expression tree re-writer if you're going to send your query off to an ORM.

What you end up with your query is something like:

Expression<Func<Person, int>> personIdSelector = person => person.PersonID;

var query = Persons
    .Select(p =>
    new {
        a = personIdSelector.Inline(p)
    })
    .ApplyInlines();

With the following expression helpers:

public static class ExpressionExtensions
{
    public static TT Inline<T, TT>(this Expression<Func<T, TT>> expression, T item)
    {
        // This will only execute while run in memory.
        // LINQ to Entities / EntityFramework will never invoke this
        return expression.Compile()(item);
    }

    public static IQueryable<T> ApplyInlines<T>(this IQueryable<T> expression)
    {
        var finalExpression = expression.Expression.ApplyInlines().InlineInvokes();
        var transformedQuery = expression.Provider.CreateQuery<T>(finalExpression);
        return transformedQuery;
    }

    public static Expression ApplyInlines(this Expression expression) {
        return new ExpressionInliner().Visit(expression);
    }

    private class ExpressionInliner : ExpressionVisitor
    {
        protected override Expression VisitMethodCall(MethodCallExpression node)
        {
            if (node.Method.Name == "Inline" && node.Method.DeclaringType == typeof(ExpressionExtensions))
            {
                var expressionValue = (Expression)Expression.Lambda(node.Arguments[0]).Compile().DynamicInvoke();
                var arg = node.Arguments[1];
                var res = Expression.Invoke(expressionValue, arg);
                return res;
            }
            return base.VisitMethodCall(node);
        }
    }
}

// https://codereview.stackexchange.com/questions/116530/in-lining-invocationexpressions/147357#147357
public static class ExpressionHelpers
{
    public static TExpressionType InlineInvokes<TExpressionType>(this TExpressionType expression)
        where TExpressionType : Expression
    {
        return (TExpressionType)new InvokeInliner().Inline(expression);
    }

    public static Expression InlineInvokes(this InvocationExpression expression)
    {
        return new InvokeInliner().Inline(expression);
    }

    public class InvokeInliner : ExpressionVisitor
    {
        private Stack<Dictionary<ParameterExpression, Expression>> _context = new Stack<Dictionary<ParameterExpression, Expression>>();
        public Expression Inline(Expression expression)
        {
            return Visit(expression);
        }

        protected override Expression VisitInvocation(InvocationExpression e)
        {
            var callingLambda = e.Expression as LambdaExpression;
            if (callingLambda == null)
                return base.VisitInvocation(e);
            var currentMapping = new Dictionary<ParameterExpression, Expression>();
            for (var i = 0; i < e.Arguments.Count; i++)
            {
                var argument = Visit(e.Arguments[i]);
                var parameter = callingLambda.Parameters[i];
                if (parameter != argument)
                    currentMapping.Add(parameter, argument);
            }
            if (_context.Count > 0)
            {
                var existingContext = _context.Peek();
                foreach (var kvp in existingContext)
                {
                    if (!currentMapping.ContainsKey(kvp.Key))
                        currentMapping[kvp.Key] = kvp.Value;
                }
            }

            _context.Push(currentMapping);
            var result = Visit(callingLambda.Body);
            _context.Pop();
            return result;
        }

        protected override Expression VisitParameter(ParameterExpression e)
        {
            if (_context.Count > 0)
            {
                var currentMapping = _context.Peek();
                if (currentMapping.ContainsKey(e))
                    return currentMapping[e];
            }
            return e;
        }
    }
}

This will allow you to re-write the expression tree before it ever gets to an ORM, allowing you to inline the expression directly into the tree.

How to return IQueryable LINQ result from two joined tables into a List string ?

Short answer:
Select only the RoleName, and use SelectMany instead of Select

Better answer

So you have a table of Roles, and a table of Users (I'm simplifying your long identifiers, not part of the problem and way too much typing).

There seems to be a many to many relation between Roles and Users: Every Role is a role for zero or more Users, every User has zero or more Roles.

This many-to-many relation is implemented using a standard junction table: UsersInRoles. This junction table has two foreign keys: one to the User and one to the Roles.

You have a UserId, and it seems that you want all names of all Roles of the user that has this Id.

How about this:

int userId = ...

// Get the names of all Roles of the User with this Id
var namesOfRolesOfThisUser = dbContext.UsersInRoles

    // only the user with this Id:
    .Where(userInRole => userInRole.UserId == userId)

    // get the names of all Roles for this userInRole
    .SelectMany(userInRole => dbContext.Roles.Where(role => role.RoleId == userInRole.RoleId)
                                             .Select(role => role.RoleName));

In words: from the table of UsersInRoles, keep only those UsersInRoles that have a value for property UserId that equals userId.

From every one of the remaining UsersInRoles, select all Roles that have a RoleId that equeals the UserInRole.RoleId. From these Roles take the RoleName.

I use SelectMany to make sure that I get one sequence of strings, instead of a sequence of sequences of strings.

If you suspect double RoleNames, consider to append Distinct() at the end.

But I want to Join!

Some people really like to do the joins themselves.

int userId = ...
var namesOfRolesOfThisUser = dbContext.UsersInRoles
    .Where(userInRole => userInRole.UserId == userId)
    .Join(dbContext.Roles,

    userInRole => userInRole.RoleId,  // from every UserInRole take the foreign key
    role => role.RoleId,              // from every Role take the primary key

    // when they match, take only the name of the Role
    (userInRole, role) => role.RoleName);

Using Iqueryable with Linq