LINQ. Who knows how to do this?

S

shapper

Hello,

I created 3 few tables that have Many to Many relationships:

Posts (PostId PK)
Files (FileId PK)

PostsTags (PostId PK, TagId PK)
FilesTags (FileId PK, TagId PK)

Tags (TagId PK, TagName)

First of all, by dragging the tables to a dbml file the classes are
not ready for such Many to Many relationship:

Posts > PostsTags > Tags
and
Files > FilesTags > Tags

Does anyone knows how to do this?

And the behavior which seems logic to me (but maybe I am wrong) should
be:
(The example for Posts. For files is the same)

- When a tag is added to a Post, a record is added to TagsInPosts.
If the Tag (given its TagName) does not exist in Tags then it is
created in Tags table.

- When a tag is removed from a post then it is deleted from PostsTags.
If that tag is no longer associated with a Post or a File through
PostsTags and FilesTags then it is deleted from Tag.

Can I implement this logic in my LINQ classes or should I need to
create triggers, besides the constrains I enumerated, in my database?

I only find simple one-to-one examples and the only many-to-many
example is back to May 2006.

Thanks,
Miguel
 
M

Mark Dykun

Miguel,

In your database do you have relationships setup. When you create your LINQ
to SQL classes it uses the relationship data to determine the shape of the
objects. I created the 5 tables that you mentioned;

Post
File
Tag
PostTag
FileTag

I then setup the relationships inside if Enterprise manager. Created a LINQ
to SQL class including all of the tables. and then wrote the following
query;

LinqTestDbDataContext db = new LinqTestDbDataContext();

db.Log = Console.Out;

var values = from file in db.Files

select new { file.FileTag.Tag };

foreach (var value in values)

{

Console.WriteLine(value.Tag.TagID.ToString());

}

with the logging on I inspected the SQL that was created for execution



SELECT [t3].[test], [t3].[TagID], [t3].[TagDescription]

FROM [dbo].[File] AS [t0]

LEFT OUTER JOIN [dbo].[FileTag] AS [t1] ON [t1].[FileTagID] = [t0].[FileID]

LEFT OUTER JOIN (

SELECT 1 AS [test], [t2].[TagID], [t2].[TagDescription]

FROM [dbo].[Tag] AS [t2]

) AS [t3] ON [t3].[TagID] = [t1].[TagID]



As you can see it does work with Many-to-Many relationships. Just make sure
that you have relationships defined in the database.


Mark
 
S

shapper

Miguel,

In your database do you have relationships setup. When you create your LINQ
to SQL classes it uses the relationship data to determine the shape of the
objects. I created the 5 tables that you mentioned;

Post
File
Tag
PostTag
FileTag

I then setup the relationships inside if Enterprise manager. Created a LINQ
to SQL class including all of the tables. and then wrote the following
query;

LinqTestDbDataContext db = new LinqTestDbDataContext();

db.Log = Console.Out;

var values = from file in db.Files

select new { file.FileTag.Tag };

foreach (var value in values)

{

Console.WriteLine(value.Tag.TagID.ToString());

}

with the logging on I inspected the SQL that was created for execution

SELECT [t3].[test], [t3].[TagID], [t3].[TagDescription]

FROM [dbo].[File] AS [t0]

LEFT OUTER JOIN [dbo].[FileTag] AS [t1] ON [t1].[FileTagID] = [t0].[FileID]

LEFT OUTER JOIN (

SELECT 1 AS [test], [t2].[TagID], [t2].[TagDescription]

FROM [dbo].[Tag] AS [t2]

) AS [t3] ON [t3].[TagID] = [t1].[TagID]

As you can see it does work with Many-to-Many relationships. Just make sure
that you have relationships defined in the database.

Mark


I created 3 few tables that have Many to Many relationships:
Posts (PostId PK)
Files (FileId PK)
PostsTags (PostId PK, TagId PK)
FilesTags (FileId PK, TagId PK)
Tags (TagId PK, TagName)
First of all, by dragging the tables to a dbml file the classes are
not ready for such Many to Many relationship:
Posts > PostsTags > Tags
and
Files > FilesTags > Tags
Does anyone knows how to do this?
And the behavior which seems logic to me (but maybe I am wrong) should
be:
(The example for Posts. For files is the same)
- When a tag is added to a Post, a record is added to TagsInPosts.
If the Tag (given its TagName) does not exist in Tags then it is
created in Tags table.
- When a tag is removed from a post then it is deleted from PostsTags.
If that tag is no longer associated with a Post or a File through
PostsTags and FilesTags then it is deleted from Tag.
Can I implement this logic in my LINQ classes or should I need to
create triggers, besides the constrains I enumerated, in my database?
I only find simple one-to-one examples and the only many-to-many
example is back to May 2006.
Thanks,
Miguel

Mark,

I am checking everything in my database and I will try again using
LINQ with Many to Many relationships.

Thanks,
Miguel
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

XML and SQL database 3
Linq. Select 1
Delete Record using Linq 3
Linq. Why do I get this error? 4
Linq. Avoid Joins 2
Filter 1
Circular Reference. I really need help ... thanks. 4
Linq. Delete Record 2

Top