Solved

LINQ collections referencing

Posted on 2009-05-05
21
275 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?
0
Comment
Question by:veruthandai
  • 9
  • 7
  • 5
21 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
If you mean you want to use the child parent relationships then LINQ is the best option to use.
0
 

Author Comment

by:veruthandai
Comment Utility
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.
0
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
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
0
 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
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

0
 

Author Comment

by:veruthandai
Comment Utility
@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

0
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
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.
0
 

Author Comment

by:veruthandai
Comment Utility
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.
0
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
It may be helpful if you elaborate a bit more in details.
0
 

Author Comment

by:veruthandai
Comment Utility
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
0
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
Yes its a pretty standard schema. The difference is that its the values that matter to you rather than names.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:veruthandai
Comment Utility
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.
0
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
You can use SQL like queries in LINQ so

From k in db.keys where k.key = "Name"
0
 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
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
0
 

Author Comment

by:veruthandai
Comment Utility
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?
0
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
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.
0
 

Author Comment

by:veruthandai
Comment Utility
I'm sorry, but no, that isn't really explaining much. You haven't looked at the database schema I've added.
0
 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
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
0
 

Author Comment

by:veruthandai
Comment Utility
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.
0
 
LVL 62

Accepted Solution

by:
Fernando Soto earned 500 total points
Comment Utility
Hi veruthandai;

Here is C# sample code using Linq to SQL to query the Keys and Units tables that have a collectio ID of 1, sample showsn here is using the join clause.

Fernando
int collectionId = 1;

DataClasses1DataContext db = new DataClasses1DataContext();
 

var results = from k in db.Keys

              join u in db.Unit on k.CollectionId equals u.CollectionId

              where k.CollectionId == collectionId

              select new { KeyId = k.Id, 

                           KeyValue = k.Value, 

                           KeyName = k.Name, 

                           UnitId = u.Id };
 

foreach (var recs in results)

{

    Console.WriteLine(recs.KeyId + "\t" + recs.KeyValue + "\t" + recs.KeyName + "\t" + recs.UnitId );

}

Open in new window

0
 

Author Comment

by:veruthandai
Comment Utility
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.
0
 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
Not a problem, always glad to help.  ;=)
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now