Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

LINQ collections referencing

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
veruthandai
Asked:
veruthandai
  • 9
  • 7
  • 5
1 Solution
 
CodeCruiserCommented:
If you mean you want to use the child parent relationships then LINQ is the best option to use.
0
 
veruthandaiAuthor 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.
0
 
CodeCruiserCommented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Fernando SotoRetiredCommented:
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
 
veruthandaiAuthor 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

0
 
CodeCruiserCommented:
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
 
veruthandaiAuthor 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.
0
 
CodeCruiserCommented:
It may be helpful if you elaborate a bit more in details.
0
 
veruthandaiAuthor 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
0
 
CodeCruiserCommented:
Yes its a pretty standard schema. The difference is that its the values that matter to you rather than names.
0
 
veruthandaiAuthor 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.
0
 
CodeCruiserCommented:
You can use SQL like queries in LINQ so

From k in db.keys where k.key = "Name"
0
 
Fernando SotoRetiredCommented:
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
 
veruthandaiAuthor 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?
0
 
CodeCruiserCommented:
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
 
veruthandaiAuthor Commented:
I'm sorry, but no, that isn't really explaining much. You haven't looked at the database schema I've added.
0
 
Fernando SotoRetiredCommented:
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
 
veruthandaiAuthor 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.
0
 
Fernando SotoRetiredCommented:
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
 
veruthandaiAuthor 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.
0
 
Fernando SotoRetiredCommented:
Not a problem, always glad to help.  ;=)
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 9
  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now