LINQ. Who knows how to do this?

[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

 

[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

 

[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

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

Mark,

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

Thanks,
Miguel