no way to unpivot data with linqtodb?

[iouris]

Hi Guys,
Love linqtodb, however I've come across an issue that I'd like to see if you have a workaround for.

I need to unpivot data, ie

var qnames =
  from person in People
  from name in
    new[] { person.Name1 }
    .Concat(new[] { person.Name2 })
  select name;

var names = qnames.ToList();

this ends up with an exception: LinqToDB.Linq.LinqException: ''<>h__TransparentIdentifier0.person.FirstName1' cannot be converted to SQL.'

This however works OK on EF 6.

I have tried using an artificial single row table, it dodnt work either.

var qnames =
  from person in People
  from name in
    SingleRow.Select(_ => person.Name1)
    .Concat(SingleRow.Select(_ => person.Name2))
  select name;

Is there any way to unpivot data with linq2db?

Thanks

[sdanyliv]

@iouris, which SQL do you expect?

[iouris]

well, any that would unpivot the data, ie

select 
  ca.Name
from Person person
cross apply (
  select person.Name1 as name
  union all
  select person.Name2 as name
) ca

[sdanyliv]

Maybe i'm missing something but query is obvious

var qnames =
  People.Select(person => person.Name1)
  .Concat(
  People.Select(person => person.Name2)
  );

[iouris]

yes, however for a complex - or expensive - query on People this isnt efficient, hence the need for cross apply.

[sdanyliv]

You are right will check what we can do here.
If you have such big query and can't wait, i can write workaround based on temporary table or other tricks.

[sdanyliv]

@igor-tkachev, from my point it should be implemented in the following way:

var qnames =
  from person in People
  from name in new[] { person.Name1, person.Name2, "someName1", @SomeParam )
  select name;

var names = qnames.ToList();

Array should be translated to UNION ALL statements.

select 
  ca.Name
from Person person
  cross apply (
    select person.Name1 as name
    union all
    select person.Name2 as name
    union all
    select "someName1"
    union all
    select @SomeParam
  ) ca

[iouris]

@sdanyliv, thank you for picking this missing feature up so quickly. Do you mind please advising me on how to get a new build of linq2db which includes it, or the best way to get the sources and build it myself.
Thanks a lot.

[sdanyliv]

You can build from our branch issue_957. Just clone our repository, switch to this branch and build linq2db.csproj by latest version of VS2017.
For now Igor is trying to refactor some core parts to make such functionality more flexible.

[iouris]

@sdanyliv - much appreciated!

[frankiDotNet]

Based on these changes, is it possible to join even complex arrays?
I am asking because I plan to create a where in extension, that applies to multiple keys:

IQueryable<T> WhereInMultiple<T, T1, T2>(this IQueryable<T> query, Expression<Func<T, T1>> prop1Expr, Expression<Func<T, T2>> prop2Expr, IEnumerable<CompositeId<T1, T2>> idList)

So that you can call something like this:

using(var db = new MyDb())
{
    var cIdList = new List<CompositeId<string, string>>() {
        new CompositeId<string, string>("Max", "Mad"),
        new CompositeId<string, string>("Pan", "Peter"),
    };
    var query = db.Persons.WhereInMultiple(p => p.Name, k => k.FirstName, cIdList);
    var result = query.ToList();
}

public class CompositeId
{
  public CompositeId();
  public CompositeId(object id1);
  public CompositeId(object id1, object id2);

  public virtual object Id1 { get; set; }
  public virtual object Id2 { get; set; }
}

 public class CompositeId<TId1, TId2> : CompositeId
 {
    public CompositeId()
    {
    }
    public CompositeId(TId1 id1, TId2 id2)
    {
       Id1 = id1;
       Id2 = id2;
    }
    public new TId1 Id1 { get; set; }
    public new TId2 Id2 { get; set; }
}

The where in contains an inner join expression (not completly finished):

public static IQueryable<T> WhereInMultiple<T, T1, T2>(this IQueryable<T> query, Expression<Func<T, T1>> prop1Expr, Expression<Func<T, T2>> prop2Expr, IEnumerable<CompositeId<T1, T2>> idList)
      {
         var memberExpr1 = (MemberExpression)prop1Expr.Body;
         var memberExpr2 = (MemberExpression)prop2Expr.Body;

         if (memberExpr1 == null)
         {
            throw new ArgumentException("Please use only MemberAccess types as expression!", "expr1");
         }
         if (memberExpr2 == null)
         {
            throw new ArgumentException("Please use only MemberAccess types as expression!", "expr2");
         }

         ParameterExpression leftParam = Expression.Parameter(typeof(T), "l");
         ParameterExpression innerParam = Expression.Parameter(typeof(CompositeId<T1, T2>), "r");
         // Try to avoid reflection!!!
         var cId1Member = typeof(CompositeId<T1, T2>).GetMember("Id1")[0];
         var cId2Member = typeof(CompositeId<T1, T2>).GetMember("Id2")[0];

         var propOuter1 = Expression.PropertyOrField(leftParam, memberExpr1.Member.Name);
         var bindOuter1 = Expression.Bind(cId1Member, propOuter1);
         var propOuter2 = Expression.PropertyOrField(leftParam, memberExpr2.Member.Name);
         var bindOuter2 = Expression.Bind(cId2Member, propOuter2);

         MemberBinding[] bingdingsOuter = {bindOuter1, bindOuter2};

         var leftNew = Expression.New(typeof(CompositeId<T1, T2>));
         var leftInit = Expression.MemberInit(leftNew, bingdingsOuter);
         var leftKey = Expression.Lambda(leftInit, leftParam);

         var innerKey = Expression.Lambda(innerParam, innerParam);

         var resultSelector = Expression.Lambda<Func<T, CompositeId<T1, T2>, T>>(
            leftParam,
            leftParam,
            innerParam);
         var join =  
               Expression.Call(
                  typeof(Queryable), "Join", new Type[] {
                     typeof (T),
                     typeof (CompositeId<T1, T2>),
                     typeof (CompositeId<T1, T2>),
                     typeof (T),
                  },
                  query.Expression, 
                  idList.AsQueryable().Expression,
                  leftKey,
                  innerKey,
                  resultSelector);


         return Expression.Lambda<Func<IQueryable<T>>>(join).Compile()();
      } 

[sdanyliv]

For me it is better to create temp table with these keys. BulkCopy records to this table and then join.

[frankiDotNet]

The problem with temp table is that you have to pre define the column types, and you have the slow down of a bulk copy (even if it is fast :-) )..
But is it theroritically possible with the new ArrayBuilder ?

[sdanyliv]

It will be slover than temp table solution. Why not create table from CompositeId<> class?

[frankiDotNet]

I will give a try to look at the performance, but the extension should be the same..

[sdanyliv]

Bad in temp table solution, that you have to drop temp tables after disposing context. Or keep in mind that they may exist or will be dropped by server after closing session.

[iouris]

@sdanyliv, thank you for the build instructions. We've been able to use your fix where it comes to single value selection, as in the original issue description:

var qnames =
  from person in People
  from name in
    new[] { person.Name1 }
    .Concat(new[] { person.Name2 })
  select name;

var names = qnames.ToList();

However it doesn't work for a slightly more complicated scenario, ie

var qpeople = context.People.Where(/* complex and expensive search */);

var qpeopleAndTheirFriends =
  from person in qpeople 
  from personOrFriend in
    new[] { person.Id, person.Name, friendOf = (int?)null }
    .Concat(
       from friendRelationship in context.FriendRelationships
       where friendRelationship.PersonId = person.Id
       join  friend in context.People on friendRelationship.FriendId equals friend.Id
       select new { friend.Id, friend.Name, friendOf = (int?)person.Id }
    )
  select personOrFriend;

var peopleAndTheirFriends = qpeopleAndTheirFriends.ToList();

Please note that it works as expected in EF (6).

We'd greatly appreciate if this could also be looked into.

Apologies, I've just realised that this issue doesn't necessarily relate to the original issue I've reported, however it is still an issue for us.