We help IT Professionals succeed at work.

LINQ collections referencing

veruthandai
veruthandai asked
on
297 Views
Last Modified: 2013-11-11
Alright, I'm trying to do something a bit abstract here...

I basically have a table for clients, tags, keys, and collections.
A collection represents a chain of keys and tags that are related.

I want to use this system for adding 'properties' to clients.

I'm curious to see how this might work in LINQ. The basic structure is as follows...

Collections
- Id

Tags
- Id
- Name
- Collection

Keys
- Id
- Name
- Value
- Collection

Clients
- Id
- Collection

Does anyone understand what I am trying to achieve? Would it be possible to set things up this way and still use LINQ? For instance creating a "Properties" field in the Client class and being able to reference the Keys/Tags associated with it?
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
If you mean you want to use the child parent relationships then LINQ is the best option to use.

Author

Commented:
Well, I'm specifically concerned about the object structure in my C# code using this approach.
I was wondering how one would tackle this. Let's assume I set up a Unit and tagged it with a Key called "Name", and I want to retrieve the Name key's value.
The logical approach is to first query the Keys then get their values - but I was wondering if things like this can be written in a more concise, less verbose manner more like accessing simple properties.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
With LINQ, you get classes corresponding to tables. So each column in the table becomes a property of the class. So For example, we have customer and orders table then

txtCustName = TheCustomer.Name
txtTotalOrders = TheCustomer.Orders.Count

etc
Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Hi veruthandai;

To your question, "I was wondering how one would tackle this. Let's assume I set up a Unit and tagged it with a Key called "Name", and I want to retrieve the Name key's value.", I am assuming that "tagged it" is a entry in the Tags table and that the field Name in both Tags and Keys tables are related in a 1 to Many relationship. You will be able to query the Tags table and retrieve the values from the Keys table in one of two ways. 1. By accessing the Name field of the Tags table and Linq will make a second query to the database automatically to get the records from the Keys table without any additional C# code. 2. You can instruct Linq to SQL to download the related records with the initial query thereby reducing the number of hits to the database but at the cost of increased network traffic.

Fernando

Author

Commented:
@FernandoSoto
Alright, that's a little more like what I'm trying to figure out - how would one go about accomplishing this? Example given below...

Keys
- Id
- Value
- Name
- CollectionId
 
Unit
- Id
- CollectionId
 
Collections
- Id
 
 
Keys
- 01 - "MyName" - "Name" - 01
 
Units
- 01 - 01
 
Collections
- 01
 
Where the Key 01 and the Unit 01 both reference collection 01 and this is how they form a relationship.

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
I think i do not fully understand your scenario but using a much familiar scenario of customer, product, order etc, i would write some classes using the <table>, <column>, <association> etc attributes. Is your question related to this implementation? You can also generate the classes using designer.

Author

Commented:
The problem is that the database is not set up with hard coded class/structures. It's ambiguous because data is merely strung together with tags and keys, and all joined by a CollectionId. It isn't earmarked by what the data is, the data is specified at runtime and during insertion.
What I'm trying to figure out is how to work with this in LINQ. I already know that LINQ will make class/properties based on the table names. But that isn't the kind of situation I have to work with.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
It may be helpful if you elaborate a bit more in details.

Author

Commented:
Sure. I'll enclose the schema.
I have another thread that references this same schema (different topic, though)
Everything is a Unit, and items are created by making Units and relating them to Keys and Values.
I'm trying to figure out how to work with this in C#/LINQ code, pretty much. The database design gives a lot of flexibility in designing structures - but the downside is that it defeats a lot of the code model that LINQ provides.

schema.jpg
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Yes its a pretty standard schema. The difference is that its the values that matter to you rather than names.

Author

Commented:
That's right. What I am trying to figure out is how to relate this most efficiently in code.
Assume I want to lookup all Units that have a Key named "Name" and find one with a specific Value in that key, that sort of thing. I'm trying to see if there is any way to do this without running 3 or 4 queries for everything.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
You can use SQL like queries in LINQ so

From k in db.keys where k.key = "Name"
Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Hi veruthandai;

Your latest post is different from the initial post. In the new DB structure if you were to query the DB what information would you query and what results do you want back?

Is this a Many to Many relationship between Keys and Units with Collections as the join table?

Fernando

Author

Commented:
Yes, this is a many to many relationship.
I'm sorry, my SQL knowledge is very limited. I'm trying hard to make due with what I've been given.
 
Collections is the join table, yes! That's exactly right.
I'm not wanting any 'specific' data. I'm trying to figure out how this approach will work in LINQ in the long term. If I can say query based on expected keys or tags, etc. Oh geez, I'm sorry this is so hard for me to explain. It's a really muddled question in and of itself.
 
Let's say I set up a "Client".
I'd make a Unit, then I would create a new CollectionId and refer to that in the Unit record.
I would create a Key with an element of "Name", and a value of "TheClientName", that key's collectionId would be the same as the Units.
I would create a tag that used the Element "Client" and its collection id would be the same as the Unit and the Key made before.
So then, looking at SQL...
I would select all items where CollectionID = 1
and I would get something like this..
UnitId - TagId - KeyId - KeyValue
1 - 1 - 1 - TheClientName
Just an example, and a bad one at that. What I am asking is .... what is the best, logical way to approach queries like this in LINQ so that I can retain a modest object structure while still giving the database this kind of flexibility?
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
LINQ has almost similar capabilities as SQL(and better in some cases). When you create associations in objects in LINQ, you can navigate those associations directly. It is very different to SQL so its confusing. For example, in Customer table, you have customerID. Similarly, in orders table, you have customerID. Now you can query both tables based on customerID using either SQL or LINQ. But in LINQ, you can navigate directly to all the orders placed by a particular customer. Similarly, you can navigate to the customer who placed a particular order. I hope it was understandable for you.

Author

Commented:
I'm sorry, but no, that isn't really explaining much. You haven't looked at the database schema I've added.
Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Hi veruthandai;

Your Collections table the way it is set up is not a Join Table for the other tables that reference it. A Join table would have the ID's of the other tables it is joining as a field in its table so that you could navigate from one, Many side, to the other, Many side. In this case you would need to use the Join clause in the Linq query to achieve what you want.

Fernando

Author

Commented:
The goal of the collections table was to move away from referencing things in other tables as much as possible. Basically everything references a collection ID and this forms all of the relationships.
I'm not sure what you mean by using a join statement in LINQ. I thought it was C# code, not SQL code.
Retired
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Yes, that's exactly what I was trying to figure out. How to understand the relationships as they persist in LINQ.
 
Thank you so much for your time, I know I made it too confusing with poor wording.
Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Not a problem, always glad to help.  ;=)
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.