Solved

LINQ collections referencing

Posted on 2009-05-05
21
278 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
ID: 24313070
If you mean you want to use the child parent relationships then LINQ is the best option to use.
0
 

Author Comment

by:veruthandai
ID: 24313806
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
ID: 24313977
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 62

Expert Comment

by:Fernando Soto
ID: 24314084
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
ID: 24314202
@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
ID: 24314226
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
ID: 24314321
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
ID: 24314498
It may be helpful if you elaborate a bit more in details.
0
 

Author Comment

by:veruthandai
ID: 24314571
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
ID: 24314678
Yes its a pretty standard schema. The difference is that its the values that matter to you rather than names.
0
 

Author Comment

by:veruthandai
ID: 24314717
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
ID: 24314846
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
ID: 24314913
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
ID: 24314972
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
ID: 24315087
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
ID: 24315130
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
ID: 24315238
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
ID: 24315278
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
ID: 24315872
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
ID: 24315911
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
ID: 24315935
Not a problem, always glad to help.  ;=)
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

786 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