LINQ to SQL - some possible issues with syntax for joins

[Phil Hepburn]

Hi Robert, and all guys,

Robert I know you told me in Cologne to by-pass 'joins' in LINQ and go directly to Entity Framework, which is correct in many respects - BUT - the code and query syntax for 'LINQ to SQL' should at least work, and so I am trying to add what I have already done and achieved in C#. Oh! and so far failed with X#!!!

I have attached some image files to try and explain my simple problem, but it is a show stopper at the moment.

Look at image '_04' and see the syntax I am trying out - the genuine join SQL statement is running in image '_02' and the C# version of what I am trying in X# is shown in image '_03' although it is a more complex query from my eNotes of 4.5 years back.

With the syntax used as in image '_04' it is if the compiler thinks I am still in the query statement, as slashes for commenting do funny things, as also sometimes the TRY / CATCH text and colouration.

As a first go, can you see / spot anything that may obviously be wrong ? I have had the separate tables working OK, as well as the anonymous types - it when I try a JOIN that the syntax seems to be lacking in some way.

HELP !!! PLEASE ???

Regards,
Phil.

JoinsL2S_04.jpg (21.8 KiB) Viewed 707 times

JoinsL2S_03.jpg (56.05 KiB) Viewed 707 times

[bob]

Phil,
I have a problem trying to understand what your problem is.
Is the problem that multiple join clauses are not working ?
Or is it something else ?

Robert

[Phil Hepburn]

Sorry Bob,

It is one of those things where we can get focused on reporting back, and not really explain fully.

When I try what I think should work it does not - but because of the limited X# Visual Studio integration I have I get little to work with in terms of feedback / errors - and the code syntax I use seems to break the TRY / CATCH structure which is also a bit of a show stopper.

Basically I try what is in image '_04' and it doesn't compile or run. Though Robert may spot something quick, as he usually does.

Cheers,
Phil.

Hope that helps,
Phil.

[robert]

Phil,

It is difficult for me to reproduce what you are doing, since I am not seeing the whole picture.
I have no idea what the problem could be.
You write that you get a compilation error. It would probably help if you show us the text of the error message.
The problem could be in :
- the parser
- the binder could have problems binding the property names
- something completely different ?

Robert

2 hours later:

In the meantime I have done some research and found the cause of the problem:
in the X# compiler we are building a C# syntax tree and send that syntax tree to the Roslyn backend.
We accidently inserted an equals token ("=") in stead of the equals keyword ("equals") in the joinclause treenodes. Roslyn does not like that and throws an exception.
I will send you an updated version of the compiler later tonight.

[Phil Hepburn]

Robert,

Thanks a lot !

I knew it was a difficult one as my recorded images (not sent) of me working at the VS editor show I get a compiler message (for what code I have at the moment) and absolutely no messages from the Error tab or anywhere else, running with the debug option has no effect - so in fact I got absolutely nothing (no help) which I could pass on to you. No errors, no warnings, and no messages.

Yes, the "=" in place of the "equals" will indeed be a bit of a hiccup I would guess ;-0)

Still, this is another great "find and fix" of yours - I am very impressed with how you have helped me and LINQ so far.

Well Done Indeed !
Cheers,
Phil.

P.S. I look forward to a compiler update and am keeping my fingers crossed (or thumbs held if you are that kind of guy.

[Phil Hepburn]

Robert,

Thanks - you are as good as your word yet again ;-0)

I downloaded your new compiler from your OneDrive link, and without too much fuss or messing, had my test Visual Studio app working just fine.

Attached is an image to show 'JOIN' code and results data displayed in a WPF data grid control.

Lines 63 through 68 are the ones with all the query syntax for joining two Tables.

And so the data grid is actually two SQL tables joined into one. This is what is called an INNER JOIN.

There is much more to JOINing than this but we can call this a great start. Thanks again for your help and support.

I will send more challenges tomorrow ;-0) As I have some right now.

But until then sleep well !

Cheers,
Phil.

P.S. in line 64 we have to place the field values in the correct order, this is the same as in C# code. The first one needs to be in the FROM line and the second one is that specified in the JOIN line. Unlike SQL itself !?

XS_RobNuCompiler_04.jpg (67.12 KiB) Viewed 707 times

XS_RobNuCompiler_06.jpg (29.38 KiB) Viewed 707 times

[Phil Hepburn]

Robert and all,

Well, before I go and post about my next issue with more complex LINQ query code, lets take a short while to see some of our current successes and use of new facilities in the most recent compiler you sent me.

Attached is an image of some query code syntax which joins two table collections on customer identity and eventually makes an object of Anonymous type, where the fields / columns are named automatically since I have not specified any particular column name myself.

The data grid display shows the data items and fields that we would expect when the query is applied to my test data.

Hope this is helpful to others. Remember this should also work on LINQ to Objects where we have two collections NOT coming from SQL Tables.

Cheers,
Phil.

P.S. now to another post with my 'query / issue of the day'.

[Phil Hepburn]

Hi Robert,

Now that the basic JOIN works for LINQ with X#, I went a step further last night and tried to write the X# syntax to do the equivalent of a Left Outer JOIN. This works in C# LINQ and I attach an image to show you.

[In case anyone is interested this Outer join approach allows us easily to find the products which have not been ordered this month. And many similar sorts of things - customers not ordering etc., etc..]

XS_RobNuCompiler_12.jpg (14.56 KiB) Viewed 707 times

Notice that the JOIN line has a further extended syntax in which the joined table rows are placed into a collection called 'LHS'. From this new collection we can identify and use the elements in the new collection. By applying the extended method we can keep empty (or missing) entries for the Order Items data.

When I try this in X# I can get a compile-able and working code section - BUT - the joined collection seems only to be the data from the first table - the second one's fields are missing.

XS_RobNuCompiler_12.jpg (14.56 KiB) Viewed 707 times

XS_RobNuCompiler_15.jpg (121.19 KiB) Viewed 707 times

XS_RobNuCompiler_14.jpg (99.2 KiB) Viewed 707 times

The images show that if I reverse the JOIN code we get a different LHS collection, where once again the collection items/elements are only the data columns from the first collection in the JOIN clause.

Now then, although the 'into LHS' actually compiles and runs, it does seem to cause havoc with the VS text editor - colours and commenting and a few more strange things which don't always seem to be consistent to my eyes.

Of the attached files image '_15' shows how a green supposedly commented code section is running and giving back results.

Can I help in any way with this issue Robert ?

Hope this helps you find the problem.
Cheers,
Phil.

[robert]

Phil,

I noticed a difference between your C# code where you are using the DefaultIfEmpty() method on the LHS and the X# where you are not using that. Could this be the reason for the different results ?

And w.r.t. the editor colors: the parser inside your compiler is a few builds ahead of the parser inside your VS integration. That could cause the problem that you are seeing.
I suspect that the editor parser chokes on the Join clause in your LINQ statement and as a result it fails to properly recognize the LINQ query and the code after it.

Robert

Robert

[Phil Hepburn]

Hi Robert,

Yes, you are right about me not mentioning the Extension Method - but I did test it, and it also has an effect on data rows and not field / column inclusions. It turns an INNER join into an OUTER join.

XS_RobNuCompiler_21.jpg (34.91 KiB) Viewed 707 times

If we look at the next two images we see that the extended method changes the 'under the hood' SQL query statement to be 'OUTER JOIN'.

XS_RobNuCompiler_22.jpg (86.78 KiB) Viewed 707 times

XS_RobNuCompiler_23.jpg (96.25 KiB) Viewed 707 times

But I still can't get it to put the joined collection columns into 'LHS3', which to my mind it should.

In this example it looks as though we get the columns from the second 'JOIN' collection, and not the first.

I don't want to know about testing the same stuff in C# with the same data Tables - can we figure it out without this excessive work on my part PLEASE ?

Hope some of this is helpful and/or makes sense.

Best regards,
Phil.