Query a database with three linked tables using associations

Question

0.00/5 (No votes)

See more:

I have a database with several tables linked using associations. Is it possible to query the database to extract records from tables two tiers down using the association? See code below?

I am trying to get values from all three tables i.e from table BOOK extract P_C and STRIKE, from table OPTIONS extract OPT_MULTIPLIER and OPT_EXPIRY, and finally from table SKEW extract SKEW_A.

I can get records one tier down, but I don't know how to get records from a table two tiers down. Here "http://imgur.com/D7kh1oE" is a link to the mapping image.

What I have tried:

C#

using (lnkTRADERDataContext DATActx = new lnkTRADERDataContext())
{

    var qryBOOK = from B in DATActx.BOOKs where B.DEAL == 1
                  select new clsTRADER()
                  {
                      P_C = B.P_C,       //main table
                      STRIKE = B.STRIKE, //main table
                      OPT_EXP = B.OPTIONs.Single(X => X.OPT_EXPIRY),        //one tier down
                      OPT_MULT = B.OPTIONs.Single(X => X.OPT_MULTIPLIER),   //one tier down
                      SKEW_A = B.OPTIONs.SKEWs.Single(X => X.SKEW_A)        // two tiers down
               };
}

Posted 23-Mar-16 0:07am

Trader999

Updated 23-Mar-16 2:33am

v8

Add a Solution

Comments

Trader999 23-Mar-16 7:02am

CHill60 - thanks for the edit. How do you put the text in code format?

Garth J Lancaster 23-Mar-16 7:18am

he hasnt posted as such - so I doubt he'll see your question ... when I do the same edit on people's questions, I usually select the code in the editor, click the 'code' link at the top of the edit pane, then select the language, then 'ok' (you can us the pre tags directly once you know what they are)

Trader999 23-Mar-16 7:29am

thanks Garth, I'll format it myself next time

Sascha Lefèvre 23-Mar-16 7:43am

It's certainly possible. But in order to show you it's essential to know a) the model and b) your predicates for what to select (best explain them in normal language).

Sascha Lefèvre 24-Mar-16 9:11am

Let's continue our discussion up here.

>> For example BOOK is linked to OPTIONS via the CONTRACT field. If one of the records in BOOK has a value of ABC123 for CONTRACT, and only one record in OPTIONS has a value of ABC123 for CONTRACT then a query should produce a record with all BOOK fields and all OPTIONS fields where BOOK.CONTRACT==OPTIONS.CONTRACT

I don't think you actually need this but, just for illustration, that would be:

from B in DATActx.BOOKs
join O in DATActx.OPTIONs on B.CONTRACT equals O.CONTRACT
select new { B, O };

Or the select clause like this instead:
select new {
Contract = B.CONTRACT,
BookID = B.ID,
BookDeal = B.DEAL,
...
OptionID = O.ID,
OptionExchange = O.OPT_EXCHANGE,
...
};

>> I should make OPTIONS.CONTRACT the primary key (and its linked to BOOK.CONTRACT in a one to many)

So one OPTION can have multiple BOOKs? In that case I have to apologize, I could have seen this earlier from your diagram but I got mislead by the name of the property "OPTIONs" of BOOK: If it's a single reference (foreign key) to one OPTION (or null) then you shouldn't use plural there (and the same goes for OPTION.SKEWs, name it SKEW instead.)

>> [..] but BOOK has nulls and this raised an error

Is there a table in which CONTRACT is unique and non-nullable? Your diagram looks like it might be the table SKEW. Then you could make CONTRACT the primary key there. And then I would remove CONTRACT from BOOK because it can be retrieved from OPTION, if there is one, and if there is no OPTION for a book it probably has no CONTRACT anyway, right? That would be database normalization (don't duplicate information), which sometimes is beneficial to ignore but you should have a good reason for it.

Does the following query work? If yes, I would assume it's the solution to your question:

var qryBOOK =
from B in DATActx.BOOKs where B.DEAL == 1
select new clsTRADER()
{
P_C = B.P_C,
STRIKE = B.STRIKE,
OPT_EXP = B.OPTIONs.OPT_EXPIRY,
OPT_MULT = B.OPTIONs.OPT_MULTIPLIER,
SKEW_A = B.OPTIONs.SKEWs.SKEW_A
};

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Garth J Lancaster · Answer 1 · 2016-03-23T00:52:00

Solution 1

In a way, its no different from SQL - you use a join clause - see this join clause (C# Reference)[^]

Posted 23-Mar-16 0:52am

Garth J Lancaster

Comments

Trader999 23-Mar-16 7:01am

thanks Garth, I was hoping that with the mapping (associations) I didn't need to use joins. For example see question on Stackoverflow. http://stackoverflow.com/questions/6749740/how-to-access-associations-in-a-linq-query

Garth J Lancaster 23-Mar-16 7:04am

well ... my thoughts are, if you explicity specify the joins, it makes your code readable...... unless you include 'what joins to what' in your code comments, how does some other poor schmo reading your code figure out the relationships ?

Garth J Lancaster 23-Mar-16 7:06am

reply # 2 - last thought - make it work, then make it pretty :-)

Trader999 23-Mar-16 7:09am

I agree with make it work and then make it pretty. However the linq visual mapping is a really easy way to see the relationships and easier to change than hard code...so I was hoping for a way to use associations instead of joins but can't get the syntax right.

Garth J Lancaster 23-Mar-16 7:20am

sorry, I guess I'd have to 'see it' pictorially - I dont get the B.OPTIONS.SKEWs

Trader999 23-Mar-16 7:26am

Is there a way to link a picture of the map to the question? Assuming not, BOOK is the first table which is linked to OPTIONS which is linked to SKEW. I'm querying BOOK and using associations between the three tables I'm trying to get values from all three tables. I'm pretty sure there's a lambda expression that will work...

Sascha Lefèvre 23-Mar-16 7:40am

You could upload an image to e.g. imgur.com and then include the link to that in your question.

Garth J Lancaster 23-Mar-16 7:43am

so you have

Table A = BOOK
Table B = Options
Table C = SKEW

- so I must be thick, because I dont get why B.OPTIONs.SKEWs and not C.Skews .. Also, one of the links I saw on associations changed the query around so that effectively, C.Skews was at the 'top level' ie at the 'from' which then picked everything up by link/association

Sascha Lefèvre 23-Mar-16 7:46am

Garth, using navigation properties is endorsed for EF (I assume that's what it is) and AFAIK (not being an EF-expert) if there are no explicit association-entities there's also no way to use a regular linq-join instead.

Trader999 23-Mar-16 8:02am

I've uploaded the linq mapping to imgur: http://imgur.com/D7kh1oE. I must be thick - can't convert it to a link in the question

Sascha Lefèvre 23-Mar-16 8:11am

Please also include an explanation of what to select into your question, explained in natural language.

Sascha Lefèvre 23-Mar-16 8:13am

(because your predicates in those "Single(..)"-expressions don't show it well because they're syntactically not correct)

Trader999 23-Mar-16 8:34am

thanks Sascha, I've updated the question, I hope this is clearer

Sascha Lefèvre 23-Mar-16 8:46am

No, it's not clear enough yet :)

You have multiple "Options" for each "Book". And you want to select the value "OPT_EXPIRY" of one of the options assigned to the book. You have to tell which one of these options should be taken for that. E.g. (made up):

OPT_EXP = B.OPTIONs.Single(option => option.SYMBOL == "blah").OPT_EXPIRY

Which would mean: There's a single option with a SYMBOL value of "blah". Take the property OPT_EXPIRY of that one and assign its value to OPT_EXP (of clsTRADER).

And then for the "Skews": A book has (potentially) multiple "Options" and one of those options has (potentially) multple skews. You need to phrase a predicate of which exact skew to select for accessing its property SKEW_A.

Sascha Lefèvre 23-Mar-16 11:20am

Please use the reply-button at the top of my comment here if you want to notify me of further additions to your question. (I don't get a notification if you use that "Have a Question of Comment"-button below.)

Trader999 23-Mar-16 19:40pm

Sascha I see what you mean, I'm still struggling. I'm sure there is a method or association isn't all that useful.

For example BOOK is linked to OPTIONS via the CONTRACT field. If one of the records in BOOK has a value of ABC123 for CONTRACT, and only one record in OPTIONS has a value of ABC123 for CONTRACT then a query should produce a record with all BOOK fields and all OPTIONS fields where BOOK.CONTRACT==OPTIONS.CONTRACT

i.e BOOK.ID,BOOK.DEAL,BOOK.STRATEGY.....etc and also OPTIONS.CONTRACT,OPTIONS.OPT_EXCHANGE,OPT_SEC_TYPE etc

If I now have one record with all fields across both tables I should be able to select which field value I need?

I should make OPTIONS.CONTRACT the primary key (and its linked to BOOK.CONTRACT in a one to many), but BOOK has nulls and this raised an error.

Trader999 23-Mar-16 18:14pm

Sascha I see what you mean, I'll put a where BOOK.CONTRACT=OPTIONS.CONTRACT line in and see what happens.

For example BOOK is linked to OPTIONS via the CONTRACT field. If one of the records in BOOK has a value of ABC123 for CONTRACT, and only one record in OPTIONS has a value of ABC123 for CONTRACT then a query should produce a record with all BOOK fields and all OPTIONS fields where BOOK.CONTRACT==OPTIONS.CONTRACT

i.e BOOK.ID,BOOK.DEAL,BOOK.STRATEGY.....etc and also OPTIONS.CONTRACT,OPTIONS.OPT_EXCHANGE,OPT_SEC_TYPE etc

If I now have one record with all fields across both tables I should be able to select which field value I need?

I should make OPTIONS.CONTRACT the primary key (and its linked to BOOK.CONTRACT in a one to many), but BOOK has nulls and this raised an error.