Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

LINQ collections referencing

Posted on 2009-05-05
21
Medium Priority
?
286 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 64

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 64

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 64

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 64

Accepted Solution

by:
Fernando Soto earned 2000 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 64

Expert Comment

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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses
Course of the Month11 days, 18 hours left to enroll

916 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