fbpx
Welcome, Guest
Username: Password: Remember me
Share your code snippets, screen shots etc. here
  • Page:
  • 1
  • 2

TOPIC: LINQ to SQL - some possible issues with syntax for joins

LINQ to SQL - some possible issues with syntax for joins 2 years 7 months ago #618

  Phil Hepburn's Avatar Topic Author Phil Hepburn Offline Posts: 742
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.

Attachments:

Please Log in or Create an account to join the conversation.

LINQ to SQL - some possible issues with syntax for joins 2 years 7 months ago #619

  bob's Avatar bob Offline Posts: 2
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

Please Log in or Create an account to join the conversation.

LINQ to SQL - some possible issues with syntax for joins 2 years 7 months ago #620

  Phil Hepburn's Avatar Topic Author Phil Hepburn Offline Posts: 742
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.

Please Log in or Create an account to join the conversation.

LINQ to SQL - some possible issues with syntax for joins 2 years 7 months ago #621

  robert's Avatar robert Away Posts: 1004
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.
XSharp Development Team
The Netherlands
This email address is being protected from spambots. You need JavaScript enabled to view it.

Please Log in or Create an account to join the conversation.

Last edit: by robert.

LINQ to SQL - some possible issues with syntax for joins 2 years 7 months ago #622

  Phil Hepburn's Avatar Topic Author Phil Hepburn Offline Posts: 742
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.

Please Log in or Create an account to join the conversation.

LINQ to SQL - some possible issues with syntax for joins 2 years 7 months ago #623

  Phil Hepburn's Avatar Topic Author Phil Hepburn Offline Posts: 742
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 !?

Attachments:

Please Log in or Create an account to join the conversation.

LINQ to SQL - some possible issues with syntax for joins 2 years 7 months ago #624

  Phil Hepburn's Avatar Topic Author Phil Hepburn Offline Posts: 742
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'.
Attachments:

Please Log in or Create an account to join the conversation.

LINQ to SQL - some possible issues with syntax for joins 2 years 7 months ago #625

  Phil Hepburn's Avatar Topic Author Phil Hepburn Offline Posts: 742
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..]



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.







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.
Attachments:

Please Log in or Create an account to join the conversation.

LINQ to SQL - some possible issues with syntax for joins 2 years 7 months ago #626

  robert's Avatar robert Away Posts: 1004
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
XSharp Development Team
The Netherlands
This email address is being protected from spambots. You need JavaScript enabled to view it.

Please Log in or Create an account to join the conversation.

LINQ to SQL - some possible issues with syntax for joins 2 years 7 months ago #627

  Phil Hepburn's Avatar Topic Author Phil Hepburn Offline Posts: 742
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.



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'.





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.
Attachments:

Please Log in or Create an account to join the conversation.

LINQ to SQL - some possible issues with syntax for joins 2 years 7 months ago #628

  Phil Hepburn's Avatar Topic Author Phil Hepburn Offline Posts: 742
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.









Please Log in or Create an account to join the conversation.

LINQ to SQL - some possible issues with syntax for joins 2 years 7 months ago #635

  Phil Hepburn's Avatar Topic Author Phil Hepburn Offline Posts: 742
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.

Please Log in or Create an account to join the conversation.

LINQ to SQL - some possible issues with syntax for joins 2 years 7 months ago #648

  robert's Avatar robert Away Posts: 1004
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
XSharp Development Team
The Netherlands
This email address is being protected from spambots. You need JavaScript enabled to view it.

Please Log in or Create an account to join the conversation.

Last edit: by robert.

LINQ to SQL - some possible issues with syntax for joins 2 years 7 months ago #649

  robert's Avatar robert Away Posts: 1004
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:
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
XSharp Development Team
The Netherlands
This email address is being protected from spambots. You need JavaScript enabled to view it.

Please Log in or Create an account to join the conversation.

LINQ to SQL - some possible issues with syntax for joins 2 years 7 months ago #661

  Phil Hepburn's Avatar Topic Author Phil Hepburn Offline Posts: 742
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.

Please Log in or Create an account to join the conversation.

LINQ to SQL - some possible issues with syntax for joins 2 years 7 months ago #662

  Phil Hepburn's Avatar Topic Author Phil Hepburn Offline Posts: 742
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.

Please Log in or Create an account to join the conversation.

LINQ to SQL - some possible issues with syntax for joins 2 years 7 months ago #663

  Phil Hepburn's Avatar Topic Author Phil Hepburn Offline Posts: 742
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.

Please Log in or Create an account to join the conversation.

LINQ to SQL - some possible issues with syntax for joins 2 years 7 months ago #664

  Phil Hepburn's Avatar Topic Author Phil Hepburn Offline Posts: 742
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'.. ]



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.
Attachments:

Please Log in or Create an account to join the conversation.

LINQ to SQL - some possible issues with syntax for joins 2 years 7 months ago #665

  Phil Hepburn's Avatar Topic Author Phil Hepburn Offline Posts: 742
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.











Please Log in or Create an account to join the conversation.

LINQ to SQL - some possible issues with syntax for joins 2 years 7 months ago #674

  Phil Hepburn's Avatar Topic Author Phil Hepburn Offline Posts: 742
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.



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 :-



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 :-



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 :-



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.
Attachments:

Please Log in or Create an account to join the conversation.

  • Page:
  • 1
  • 2