Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Join with dependent subquery? #43

Closed
AndrewZenith opened this issue Sep 5, 2016 · 4 comments
Closed

Join with dependent subquery? #43

AndrewZenith opened this issue Sep 5, 2016 · 4 comments

Comments

@AndrewZenith
Copy link

AndrewZenith commented Sep 5, 2016

Question:
Is it possible to do a dynamic join with a dependent sub-query? I'm trying to do the equivalent of this MySQL SQL statement (would be outer cross apply on SQL Server) where I want to join only to a single value on another table where there may be multiple rows that match by certain filter and order criteria:

SELECT * FROM ABC `A`
LEFT OUTER JOIN CONSIGNMENT_TRACKING `AB`
ON `AB`.`SQL_RFA` = (SELECT `SQL_RFA` FROM CONSIGNMENT_TRACKING `ABA` FORCE INDEX(INDEX_01) WHERE( `ABA`.`TRKJOB$$`= `A`.`TRKJOB$$`) ORDER BY ABA.`MILESTONE$$` LIMIT 1)
GROUP BY A.`TRKREF$$`

I thought something like this might work (different tables to above):

a = a.Join(dyn1, "new ( JobID as f1, \"EVENT1\" as f2 )", "new ( JobID as f1, EventID as f2 )", $"new ( {outer}, Max(inner.SQL_RFA) as e{pass}rfa)");
a = a.Join(dyn1, $"e{pass}rfa", "SQL_RFA", $"new ( {outer}, inner as e{pass})");

But it doesn't like the Max(inner.SQL_RFA)

@StefH
Copy link
Collaborator

StefH commented Sep 5, 2016

If possible, rebuild your question into a unit-test which can be included in the tests.

@AndrewZenith
Copy link
Author

That would be difficult as I don't know how to do it. I'll try again though, this is perhaps a simpler example linq query (that works) that I want to do, but can it be done using the join extension using the string parameters?

IQueryable a = from zz in dyn2
join yy in dyn on dyn.Where(xx => xx.JobID == zz.JobID && xx.EventID == "EVENT2").Max(xy => xy.SQL_RFA) equals yy.SQL_RFA
select new { zz, yy };

@AndrewZenith
Copy link
Author

AndrewZenith commented Sep 6, 2016

I've downloaded your source and managed to write an example in the code similar to your join test. Here it is:

using System.Collections.Generic;
using System.Linq.Dynamic.Core.ConsoleTestApp.net452.Entities;

namespace System.Linq.Dynamic.Core.ConsoleTestApp.net452
{
    class Program
    {
        static void Main(string[] args)
        {
            Person magnus = new Person() { id=1, name = "a" };
            Person terry = new Person { id=2, name = "b" };

            Pet barley = new Pet { id = 1, name = "1p1", personid = 1 };
            Pet boots = new Pet { id = 2, name = "1p2", personid = 1  };
            Pet whiskers = new Pet { id = 3, name = "2p1", personid = 2 };


            var people = new List<Person> { magnus, terry };
            var pets = new List<Pet> { barley, boots, whiskers };

            //Act
            var realQuery = people.AsQueryable().Join(
                pets,
                person => 
                pets.Where(x => x.personid == person.id).Max(x => x.id),
                pet => pet.id,
                (person, pet) => new { OwnerName = person.name, Pet = pet.name });

            var realResult = realQuery.ToArray();

            var dynamicQuery = people.AsQueryable().Join(
               pets,
               "???",
               "id",
               "new(outer.name as OwnerName, inner.name as Pet)");

            var dynamicResult = dynamicQuery.ToDynamicArray<DynamicClass>();

        }
    }

    internal class Pet
    {
        public int id { get; set; }
        public int personid { get; set; }
        public string name { get; set; }
    }

    internal class Person
    {
        public int id { get; set; }
        public string name { get; set; }

    }
}

StefH added a commit that referenced this issue Sep 15, 2016
@StefH
Copy link
Collaborator

StefH commented Sep 15, 2016

It's not possible to use external sub-query in a dynamic query.

@StefH StefH closed this as completed Sep 15, 2016
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Development

No branches or pull requests

2 participants