LINQ to SQL - some possible issues with syntax for joins

[Phil Hepburn]

Hi Robert,

I have done some more work, research, and testing, on the issue of Joins (Outer) in the X# code syntax for LINQ.

It would appear that the LINQ statement for the Left Outer Join - using extended method 'DefaultIfEmpty()' works fine in creating the correct SQL query statement to select the data set - behind the scene / under-the-hood.

I have done a simple 'trick' and displayed the query (string) made in X# in a TextBox on the WPF form, I then copied and pasted this into my Management Studio, and BINGO ! - we got the returned data set.

The only problem I had to fix was that because of my issues in code, I could not choose to select the fields / columns in the second collection. So I simply typed a couple more fields manually into the query code in MS. This shows nicely in image number '_34'. So the extension method is working. Just the 'into LHS3' type of stuff is not doing what I think it should, and make a joined Table set.

In fact, now I know that its working, apart from displaying the required second collection fields, I can look at the data grid and see that the ten rows of NULLS which look blank, are actually there. So we are closer than I thought - see one attached image on this.

If only I could have the second set of fields in the joined table collection !

I hope this makes sense to you - I know I have had my head in it a lot, so it does to me ;-0)

Hope you can do something to move me forward.

Best regards,
Phil.

XS_RobNuCompiler_34.jpg (202.47 KiB) Viewed 527 times

XS_RobNuCompiler_31.jpg (120.83 KiB) Viewed 527 times

XS_RobNuCompiler_32.jpg (24.24 KiB) Viewed 527 times

XS_RobNuCompiler_33.jpg (54.29 KiB) Viewed 527 times

XS_RobNuCompiler_35.jpg (23.5 KiB) Viewed 527 times

[Phil Hepburn]

Hi Robert,

I have done yet MORE more work, research, and testing, on the issue of Joins (Inner and Outer) in the X# code syntax for LINQ.

It looks very much to me that the .... 'INTO aTest' at the end of the JOIN clause is not fully implemented (or working correctly).

Of the two collections supplied - first (FROM) and second (JOIN) - only the fields from the second of these is made available in the joined collection (third).

PLEASE can you and/or your Team have a look, as it is something which I will need to overcome, and Method syntax and Lambda expressions are not really a way around this for me and my session eNotes.

Fingers crossed, (and Thumbs held !),
Phil.

[robert]

Phil,
I will see what I can do.
However it would help if you could send me some of your sample code. It is quite difficult <g> to copy and paste the source from your images.

Robert

[robert]

Phil,

I have adjusted a C# example that I found on the web. This seems to do in X# what it does in C# as well:

Code: Select all

USING system.Collections.Generic
USING SYstem.Linq
CLASS Person
	PROPERTY FirstName AS STRING AUTO
	PROPERTY LastName AS STRING AUTO
END CLASS
CLASS Pet
	PROPERTY Name AS STRING AUTO
	PROPERTY Owner AS Person  AUTO
END CLASS

FUNCTION Start AS VOID
	VAR magnus := Person{}{FirstName := "Magnus", LastName := "Hedlund"}
	VAR terry := Person{}{FirstName := "Terry", LastName := "Adams"}
	VAR charlotte := Person{}{FirstName := "Charlotte", LastName := "Weiss"}
	VAR arlene := Person{}{FirstName := "Arlene", LastName := "Huff"}
	VAR barley := Pet{}{Name := "Barley", Owner := terry}
	VAR boots := Pet{}{Name := "Boots", Owner := terry}
	VAR whiskers := Pet{}{Name := "Whiskers", Owner := charlotte}
	VAR bluemoon := Pet{}{Name := "Bluemoon", Owner := terry}
	VAR daisy := Pet{}{Name := "Daisy", Owner := magnus}
	VAR People := List<Person>{}{magnus, terry, charlotte, arlene}
	VAR Pets    := List<Pet>{}{	barley, boots, whiskers, bluemoon, daisy}

	VAR query := FROM person IN People ;
 				  JOIN Pet IN Pets ON person EQUALS Pet:Owner INTO GJ  ;
				  SELECT  CLASS {OwnerName := Person:FirstName, Pets := GJ}
	FOREACH VAR v IN Query
		Console.WriteLine("{0}",v:OwnerName)
		FOREACH Pet AS Pet IN v:Pets
			Console.WriteLine("  {0}", Pet:Name)
		NEXT
	NEXT			
	RETURN

The Group Join GJ can be used in the anonymous type without problems as you can see in the example
I am not sure if this is what you want or expect...

Robert

[Phil Hepburn]

Thanks Robert,

I will look at making a sample of what I think is NOT correct, and will also look at your next posting of the adjusted web sample you mention.

We need to be aware that there could / will be a difference "under the hood" of LINQ in Objects to LINQ with SQL.

Its the LINQ with SQL (L2S) which has given me grief. Also I would suggest if L2S is not right in any way for JOINs the LINQ to Entities will also have an issue.

Regards and speak soon,
Phil.

[Phil Hepburn]

Robert,

I have just looked in more detail - you are not using LINQ to SQL in the example but hard wired data so you are using LINQ to Objects. Which when a I did it a few weeks back seemed to have no issues with.

Different things happen when we have the compiler create a SQL statement for the database engine, and then use the returned data set - to what happens in Objects.

I will write a C# sample to compare L2s with the X# code - its a bit of work but hey!

Cheers,
Phil.

[Phil Hepburn]

Robert,

I have thought about this some sine my last post - I think the best / smart way around this is if I create two Tables in my SQL database for the same Person and Pet data as you show here.

Then I can use the same code exactly as you show for your L2O (LINQ to Objects) example.

Does that sound sensible ? Then I can keep my head out of C# for the moment. I have gotten used in the past weeks to this nice X# syntax and don't wish to do a swap around of curly braces use ;-0)

Cheers,
Phil.

[Phil Hepburn]

Hi Robert,

All is now well - I think!

I used your adapted web example to go back to basics for JOINs, both INNER and OUTER.

Now I have both the Person/Pet example working OK here, as well as my own 'Stock' database sample producing the 'empty values. It is easy to confuse oneself, if you have not been into LINQ and SQL and JOIN stuff recently ;-0)

I have to confess that it was all my own fault - I had misread / misunderstood some of my own LINQ eNotes from four or more years back ;-0) Seemed easy then.

I obviously did not realise what structure the JOIN collection was (INTO xyz) - its NOT both the primary and secondary collections - just the data of the secondary one. So we can only access fields/columns from the secondary collection. This all makes a lot more sense in working code.

I attach one image as a demonstration / illustration - its my Stock example - we can see the ten empty entries at the end of the displayed list :-

[ ..note the code line 314 and the use of 'c:' and 'myl2'.. ]

XS_LOJoins_01.jpg (167.67 KiB) Viewed 527 times

Now I can happily get on with my research and development for the session material ;-0)

Thanks again for your help and support - much appreciated.

Cheers,
Phil.

[Phil Hepburn]

Hi Robert (and all),

Although I had to re-structure the classes in this example you posted - it did the trick for me, and got me to work out the X# syntax for doing a double join in LINQ to SQL.

Yes, three Tables in a SQL database and two inner joins to bring the relevant rows together.

The few small images should show that I had classes of Person, Pet and PetType. All of these have simple properties, meaning no business class item collections making them an entity. The original example had a collection of user defined objects. I had to flatten the example so that it fitted the 'LINQ to SQL' work which I was researching and writing about.

Here are the details - I have only included the X# code for 'PetType', as the database details from Management Studio will show enough of the others.

Oh! and as well as this multiple JOIN, I have the "Left Outer Join" (LOJ) working for this sample, also from the SQL database, not the in-memory object collection.

So we are a lot farther on I feel ;-0)
Thanks for your help and support - it has made me get positive results for you and the readers.

Must go and eat ..........
Cheers,
Phil.

XS_RobExampJoin_07.jpg (64.77 KiB) Viewed 527 times

XS_RobExampJoin_02.jpg (26.48 KiB) Viewed 527 times

XS_RobExampJoin_01.jpg (26.85 KiB) Viewed 527 times

XS_RobExampJoin_05.jpg (61.57 KiB) Viewed 527 times

XS_RobExampJoin_03.jpg (107.53 KiB) Viewed 527 times

XS_RobExampJoin_04.jpg (45.41 KiB) Viewed 527 times

[Phil Hepburn]

Hi Robert (and all),

Since you read the post of my problems some days ago, I had better tell you of my progress and solutions to all my JOIN syntax issues ;-0)

It seems that I can now (with LINQ to SQL) do as I wish and get stuff to work for me. The reason for my current successes is that I understand the 'scope' of the collection elements (items) used in the body of the query being defined.

In the next image I have commented the simple rule I found - yes, find each 'FROM@ and the item/element after the 'from' is the item in scope for the 'select' clause - use the items there.

XS_joins_scope_03.jpg (78.34 KiB) Viewed 527 times

The general code structure of a query for INNER Joins can be simple enough and we do not need to use 'into' and 'from' see the detail of the following image :-

XS_joins_scope_01.jpg (88.37 KiB) Viewed 527 times

Without the 'into' at the end of the 'join' clause we need to use the item immediately after the 'join' word - in this example they are 'pt' and 't' 9as well as the first 'pn').

The next image '_02' shows us what happens if we add the 'into' but don't change the items used in 'select' check this out :-

XS_joins_scope_02.jpg (116.23 KiB) Viewed 527 times

What is needed is a change in the code to use the items in the bunch/group made by the 'into'. So in this case we need to use 'iPP' instead of 'pt', and also 'iPPT' in place of 't'.

Obviously, we can use the full item (business object) or its properties - in the 'select' statement.

The next image '_04' shows the case where we are using object properties :-

XS_joins_scope_04.jpg (106.2 KiB) Viewed 527 times

If we refer back to our first image we see that line 523 uses the full objects in the select clause - AND - I have declared and defined a new business object of my own, called 'AllThree' where its properties are Person, Pet and PetType objects.

I hope this may clear up some of the confusion I cause earlier, BUT, this stuff does all work nicely in X#, and the new facilities in the compiler provided by Robert and his team for instantiating objects, anonymous or not, as well as collections, are just GREAT !!!

Notice that image '_04' is actually showing two Left Outer Joins, hence the use of 'DefaultIfEmpty()' extension method. The results reflect this, with empty column entries in the last two rows.

If you have any questions just ask.

Cheers,
Phil.