Hi,

I'm looking for the best way to write a query with Linq and LLBLGen 2.6. I have 4 tables that are linked as this: T1 - T2 - T3 - T4 I want to create a linq query to retrieve data from T2 with as criteria's T1.current like a date and T4 like T4 identifier. I also try to implement the left join on T4.

I m looking for how can I retrieve my T2 entity instance with the possibility to display information about my fourth tables.

Here is the first test that I have done, but this one don't resolve my problems explained above!

                var query = (from table2 in metadata.T2
                             join table1 in metadata.T1.Where(t=>t.Current==MyDate) on table2.Id equals table1.Id
                             join table3 in metadata.T3 on table2.Id equals table3.Id
                             join table4 in metadata.T4.Where(d=>d.Id == 2) on table2.Id equals table4.Id 
                             select table2);

Could you help me?

Best regards,

Do you mean that you want to prefetch the entity structure, such that in code you can reference

string name=  T1Entity.T2Entity.T3Entity.T4Entity.Name

This is done in Linq using the .WithPath operator. See the documentation here

Matt

Hi,

Thanks for your reply. Yes I want to prefetch all the structure. I just have tested with the documentation that you specified to me but the problem is the prefetch doesn't take in account my filters and when I look in my relation with T4 I have all records and I only want to retrieve the record from T4.id == 2 and not all.

Why the prefetch doesn't take in account my specified filters in the query ? I'm also looking for How can I do a left join on one relation ?

Could you help me to understand this case ?

Thanks a lot for your support,

Best regards,

Can you try something like...

 var query = (from table2 in metadata.T2
                             join table1 in metadata.T1on table2.Id equals table1.Id  Where(table2.Current==MyDate) 
                             join table3 in metadata.T3 on table2.Id equals table3.Id
                             join table4 in metadata.T4 on table2.Id equals table4.Id Where(table4.Id == 2) 
                             select table2);

If this does not work, please can you post the generated SQL in both cases so we can see what it thinks it is up to ?

The query is ok but the prefetch is the problem. If I implement the prefetch like this is it correct ?

List<IPathEdge> paths = new List<IPathEdge>(){
new PathEdge<T1>(T2Entity.PrefetchPathT1)
}
query = query.WithPath(paths.ToArray());

Thanks for your help

Best regards,

Yes, you can do that. The documentation link posted by Matt explains in a little more detail the possibilities. Would be helpful if you post the approximate sql you want to reproduce or the code you have so far so we can assist you there

Ok here is the expected SQL Statement :

SELECT * FROM Table2 t2
INNER JOIN Table1 t1 ON  t1.Id = t2.Id
INNER JOIN Table3 ON t3.Id = t2.Id.Id
LEFT JOIN Table4 ON t4.Id = t2.Id.Id
WHERE t1.CurrentDate = :paramDate
AND t4.Id = : paramId

When I fetch (all my structure) the t1 and t4 I also want to take in account the filter in the query.

Thanks for your support,

Best regards,

From your query, I think your code should looks like:

var query = (from table2 in metadata.T2
                    join table1 in metadata.T1 on table2.Id equals table1.Id
                    join table3 in metadata.T3 on table2.Id equals table3.Id
                    join table4 in metadata.T4 on table2.Id equals table4.Id
                    where t1.Current == MyDate && t4.Id ==2
                    select table2)
                       .WithPath(new PathEdge<T1Entity>(
                              T2Entity.PrefetchPathT1));

And, if your entities are actually related (the relations exits at LLBLGen Designer) the code could be:

var query = (from table2 in metadata.T2
                    where t2.Current == MyDate && t2.t3.t4.Id == 2
                    select table2)
                       .WithPath(new PathEdge<T1Entity>(
                              T2Entity.PrefetchPathT1));

Above options will return all T2 entities where t2.t1.Current == MyDate and t2.t3.t3.Id == 2. Added to that, the result will prefetch the T1 entity/entities related to each T2 entity.

Note that you can place the filter in a variety of places. That depends on how you want your results. For example:

At join level (this would generate a custom filter join). This should work but isn't needed in your case:

join table1 in metadata.T1.Where(t=>t.Current==MyDate) on table2.Id equals table1.Id

At query level. This will filter the whole results.

var query = (from table2 in metadata.T2
                    where t2.t1.Current == MyDate && t2.t3.t4.Id == 2
                    select table2)
                       .WithPath(new PathEdge<T1Entity>(
                              T2Entity.PrefetchPathT1));

At prefetchPath level. In this case you will return all entities. And, for each entity you will get the involved related path filtered. For example: all customers prefetching only the orders whose employeeId is equal to 3:

var query = (from table2 in metadata.T2
                    select table2)
                       .WithPath(new PathEdge<T1Entity>(
                              T2Entity.PrefetchPathT1, tt => tt.Current == MyDate));

Hi,

Ok Thanks for your reply. How can I now specify a left join with a criteria on this query ?

Thanks in advance,

Left joins are done through .DefaultIfEmpty. That's a linq specific stuff, you should find more info on a linq tutorial or book. An example could be:

var q = from c in metaData.Customer
        join o in metaData.Order on c.CustomerId equals o.CustomerId into oc
        from x in oc.DefaultIfEmpty()
        where x.OrderId == null
        select c;

For more examples please download the LLBLGen Pro RuntimeLibraries source code and open the /Sourcecode/LinqUnitTests

Hi,

Thanks a lot for yuour reply. I already have done this and under Oracle and don't found any left join, outer join or (+) on the relationship.

For example if I do :

var q = from c in metaData.Customer
        join o in metaData.Order on c.CustomerId equals o.CustomerId into oc
        from x in oc.DefaultIfEmpty()
        where x.OrderId == null
        select c;

The SQL Generated for the relationship between Customer and Order looks :

FROM Customer, Order WHERE Customer.CustomerId = Order.CustomerId AND Order.OrderId IS NULL

Is there any special keyword to use with Oracle ?

Thanks in advance,

Try to select NEW set of fields rather than selecting these of the Customer (c), as shown below:

var q = (from c in metaData.Customer
         join o in metaData.Order on c.CustomerId equals o.CustomerId into og
         from x in og.DefaultIfEmpty()
         select new
         {
             c.CustomerId,
             c.CompanyName,
             o.ShipRegion
         }).Distinct();

P.S. you can lose the Distinct().

Hi,

Same result as previous post.

Thanks,

If you're using Oracle 10g, use the Oracle 10g DQE. If you're using the ODP.NET 9.2 DQE, you've to set a setting in the config file of your application

Furthermore, you should really specify essential information as stated here: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=12769 and: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7717

Back to your initial question: it's been answered a couple of times but it seems what's been answered isn't the real question. It's unclear to me what exactly it is that you've a problem with. Be aware that this forum is for Linq to LLBLGen Pro related questions, not Linq specific questions, as Linq is a microsoft technique and it's therefore explained in the MSDN and in a few books like Linq in Action, so how to do a Left join is basic linq material.

As it's currently not clear what the state of your code is, which runtime lib BUILD NUMBER you're using, which database version (8i/9i/10g/11g), which ODP.NET version you're using, I've to ask you to provide more information, provide a small code snippet of your CURRENT code, which shows what you're doing and explain what you want to do.

For example: in your initial question you talk about displaying data, but it turns out later that you want to prefetch, however do you really want to? I think you just want a flat list. Please understand that it's hard for us to answer you properly if you are changing what you're fighting with during the thread.

As you've asked a lot of questions in the past days (which is fine), I've to ask you something too: please take it one step at a time. Working with an O/R mapper can take some time to getting used to the way it lets you work with data.

Hi,

Ok it's seems to be solved now. Thanks a lot for your support,

Best regards,