I know this is slightly off topic but does anybody know why the following code would not generate a left outer join in LINQ to SQL? Instead it generates n+1 queries. 1 for the eq_baskets query and then n queries for each eq_baskets record in order to grab the eq_orders records that are related to the eq_baskets record (eq_basket has a 1-m relationship with eq_orders):

       FooDataContext tdc = new FooDataContext();

        tdc.Log = Console.Out;

        //example 1
        var q = from b in tdc.eq_baskets
                where b.brkr_code_1 == "foo"
                select new
                {
                    BrokerCode = b.brkr_code_1,
                    Orders = from o in tdc.eq_orders
                             where b.basket_no == o.basket_no
                             select o
                };

        foreach (var group in q)
        {
            Console.WriteLine("BrokerCode: {0}", group.BrokerCode);
            foreach (var order in group.Orders)
                Console.WriteLine("  - {0}", order.security_name);
        }

        //example 2
        var q1 = from b in tdc.eq_baskets
                 where b.brkr_code_1 == "foo"
                 select new { BrokerCode = b.brkr_code_1, Orders = b.eq_orders };


        foreach (var group in q1)
        {
            Console.WriteLine("BrokerCode: {0}", group.BrokerCode);
            foreach (var order in group.Orders)
                Console.WriteLine("  - {0}", order.security_name);
        }

        //example 3
        var q2 = from b in tdc.eq_baskets
                 join o in tdc.eq_orders on b.basket_no equals o.basket_no into orders
                 where b.brkr_code_1 == "foo"
                 select new { BrokerCode = b.brkr_code_1, Orders = orders };


        foreach (var group in q2)
        {
            Console.WriteLine("BrokerCode: {0}", group.BrokerCode);
            foreach (var order in group.Orders)
                Console.WriteLine("  - {0}", order.security_name);
        }

No offence, but linq to Sql is really general chat material

What you run into is Linq to Sql's crappy implementation of hierarchical fetches. Wait 2 weeks or so, and you'll see how Linq to LLBLGen Pro shows how it should be done

They don't have proper merge routines, so if you have a parent set which relates to a child set over a relation, they can't merge that, so what they do instead is postpone the inner query (the orders query in your projection) till 'Orders' is accessed in the set of returned objects. It then executes the query.

the nested set is always fetcheable with prefetch path-like queries: the nested query already contains the correlated relation which ties parent to child set. What's also known is the destination of the child set, so it's doable to build a hierarchical projection which executes these nested queries in tandem with the root query, similar like prefetch paths do. As merge routines are already written in LLBLGen Pro, merging the two sets isn't that hard

Linq to Sql has some clever things but at the same time some very big flaws I simply can't understand why they dropped the ball so big. This example is one of them (it fails in the same way LoadOptions fails with a graph with 2 or more edges). Another one is for example no support for left/right shifts. Tiny detail perhaps but shifting x n bits to the left is equal to x * 2^n, ANY freshman CS student will tell you that.

Yesterday I was testing Contains() queries with tuple tests: var q = (from c in metaData.Customer select new Pair<string, string>() { Value1 = c.CompanyName, Value2 = c.Country}).Contains(new Pair<string, string>("Some companyname", "USA"));

This uses the same code as when you use Contains inside the query in a where for example. Linq to sql can't translate the Pair<> value in this query. Just property-value matching. These kind of queries make linq very usable. Not supporting these kind of things is IMHO missing a chance. Oh well. (the above works in linq to llblgen of course )

Frans,

I had been reading your blog about your progress in the LLBLGEN LINQ support and had been only semi-theoretically following what you were saying because I hadn't written any LINQ code yet. Now, a couple of days ago I started writing some LINQ code and see the enormous potential but also starting to understand some of the things you were saying. I am getting very frustrated trying yo figure out how to accomplish something that would have been very straighforward in sql.

After I posted the message here I tried even harder to figure out how to get the single outer join query working and finally did, but I find the syntax very unintuitive:

        //example 4
        var q3 = from b in tdc.eq_baskets
                 join o in tdc.eq_orders on b.basket_no equals o.basket_no into orders
                 from x in orders.DefaultIfEmpty()
                 where b.brkr_code_1 == "foo"                   
                 select new { Basket = b, Orders = x };

        int i = 0;
        foreach (var group in q3)
        {
            Console.WriteLine("Number: {0}; BrokerCode: {1}; BasketNo: {2}; SecurityName: {3}", ++i, group.Basket.brkr_code_1, group.Basket.basket_no, group.Orders == null ? "" : group.Orders.security_name);
        }

The key for me is to not do this with LoadOptions (although - it seems that you're saying LoadOptions doesn't even work as advertised). Because my understanding is that in order to have an outerjoin generated from LoadOptions the lambda expression in the LoadOptions method needs to be able to refer to the 1-1 or 1-m relationship as a member attribute of the entity that's the left side of the join. This means you have to already have the relationship in metadata. In my case, I have a legacy database that has none of the relatioships defined as foreign key constraints only has indexes for performance. So, I need to be able to do this strictly using joins until I slowly but shurely add virtual relationships in the designer...

I know LLBLGEN relies on metadata from its designer, too, so I am thinking that, even though LLBLGEN's run-time query generation engine is significantly more sophisticated it still needs to rely on relationships, right?

tolgaerdogus wrote:

Frans,

I had been reading your blog about your progress in the LLBLGEN LINQ support and had been only semi-theoretically following what you were saying because I hadn't written any LINQ code yet. Now, a couple of days ago I started writing some LINQ code and see the enormous potential but also starting to understand some of the things you were saying. I am getting very frustrated trying yo figure out how to accomplish something that would have been very straighforward in sql.

Yes, in doesn't really make the learning curve smaller in many cases.

However it does make things easier with powerfull functionmappings, which I'll discuss in my upcoming next post about Linq to LLBLGen Pro

After I posted the message here I tried even harder to figure out how to get the single outer join query working and finally did, but I find the syntax very unintuitive:

        //example 4
        var q3 = from b in tdc.eq_baskets
                 join o in tdc.eq_orders on b.basket_no equals o.basket_no into orders
                 from x in orders.DefaultIfEmpty()
                 where b.brkr_code_1 == "foo"                   
                 select new { Basket = b, Orders = x };

        int i = 0;
        foreach (var group in q3)
        {
            Console.WriteLine("Number: {0}; BrokerCode: {1}; BasketNo: {2}; SecurityName: {3}", ++i, group.Basket.brkr_code_1, group.Basket.basket_no, group.Orders == null ? "" : group.Orders.security_name);
        }

Yes, the DefaultIfEmpty() is one of the most dumbest things I've seen in years. It all comes down from the fact that the join method only understands equals. WHY... I have no idea but I fail to see why this is.

Most of the syntax is meant for linq to objects, which is a shame because it's a leaky abstraction.

The key for me is to not do this with LoadOptions (although - it seems that you're saying LoadOptions doesn't even work as advertised).

LoadOptions are the way of Linq to Sql to fetch graphs, so if you fetch customers you also want to fetch orders for example. They don't support multiple 1:n edges in the graph, which is silly. If you do add multiple 1:n edges (customer - order - order details) you'll get one of the edges prefetched and the others are lazy loaded (== sloooooooooow)

Because my understanding is that in order to have an outerjoin generated from LoadOptions the lambda expression in the LoadOptions method needs to be able to refer to the 1-1 or 1-m relationship as a member attribute of the entity that's the left side of the join. This means you have to already have the relationship in metadata. In my case, I have a legacy database that has none of the relatioships defined as foreign key constraints only has indexes for performance. So, I need to be able to do this strictly using joins until I slowly but shurely add virtual relationships in the designer...

In v2.6 of llblgen pro you can too add relations in code pretty easily, and in linq it's easy too.

I know LLBLGEN relies on metadata from its designer, too, so I am thinking that, even though LLBLGEN's run-time query generation engine is significantly more sophisticated it still needs to rely on relationships, right?

If you want to use related data in your query, yes it has to. So if you want to do: var q = from c in metaData.Customer where c.Orders.Count() > 10 select c;

you have to have the customer - order relation defined in the meta-data and have 'orders' mapped onto that.