ASP.NET Linq help

[Izi]

I want to insert a new blog post. Each blog post has category mappings, where a blog can be associaed to multiple categories:

So how do I go about a 1 to many insert in Linq?

 

[Izi]

Ignore this, I just needed to set primary keys on the category mapping table

 

[Izi]

If you set them both as primary keys linq will remove the table from the diagram and just show a direct mapping... incase you dont know

I have set them both as primary keys....

They now show in the intellisense which wasnt happening before is that what you mean?

Also I have another question, this one a little more complicated.

I have a static member which retrieves a blog entry:

public static BlogEntry GetBlogEntry(int id)
        {
            using (BlogDataContext db = new BlogDataContext())
            {
                return db.BlogEntries.Where(b => b.BlogID == id).FirstOrDefault();
            }
        }

This works fine.

I then have this code to save a new record, or update a record:

DateTime ds = Helper.BuildDateTime(txtDate.Text, TimePicker1.GetTextHour.ToString(), TimePicker1.GetTextMin.ToString(), TimePicker1.GetTextSecond.ToString());
        BlogEntry be;
        bool IsNew = false;
        be = Blog.GetBlogEntry(Helper.QueryStringInt("id"));
        if (be == null)
        {
            IsNew = true;
            be = new BlogEntry();
        }

        if (Helper.QueryStringInt("id") >= 1)
            be.BlogID = Helper.QueryStringInt("id");

        be.BlogTitle = txtTitle.Text;
        be.BlogText = txtContent.Text;
        be.BlogDate = ds;
        be.Deleted = false;
        be.Published = chkPublish.Checked;
        be.AllowComments = chkComments.Checked;
        be.FriendlyURL = txtFriendlyURL.Text;
        be.MetaDescription = txtMetaDescription.Text;
        be.MetaTitle = txtMetaTitle.Text;
      
        

        foreach (ListItem li in chkCategories.Items)
        {
            BlogCategoryMapping bcm = new BlogCategoryMapping();

            bcm.BlogID = be.BlogID;
            bcm.CategoryID = Convert.ToInt32(li.Value);

            be.BlogCategoryMappings.Add(bcm);
        }

        

        BlogDataContext db = new BlogDataContext();
        if (IsNew)
            db.BlogEntries.InsertOnSubmit(be);
        db.SubmitChanges();
        db.Dispose();

Now this works fine for adding, however editing doesnt. THe reason for this is this line:

be = Blog.GetBlogEntry(Helper.QueryStringInt("id"));

To get it working I have to get the blog entry with in the context scope set in the save method like so:

DateTime ds = Helper.BuildDateTime(txtDate.Text, TimePicker1.GetTextHour.ToString(), TimePicker1.GetTextMin.ToString(), TimePicker1.GetTextSecond.ToString());
        BlogEntry be;
        bool IsNew = false;
BlogDataContext db = new BlogDataContext();
        be = db.BlogEntries.Where(b => b.BlogID == id).FirstOrDefault();
        if (be == null)
        {
            IsNew = true;
            be = new BlogEntry();
        }

        if (Helper.QueryStringInt("id") >= 1)
            be.BlogID = Helper.QueryStringInt("id");

        be.BlogTitle = txtTitle.Text;
        be.BlogText = txtContent.Text;
        be.BlogDate = ds;
        be.Deleted = false;
        be.Published = chkPublish.Checked;
        be.AllowComments = chkComments.Checked;
        be.FriendlyURL = txtFriendlyURL.Text;
        be.MetaDescription = txtMetaDescription.Text;
        be.MetaTitle = txtMetaTitle.Text;
      
        

        foreach (ListItem li in chkCategories.Items)
        {
            BlogCategoryMapping bcm = new BlogCategoryMapping();

            bcm.BlogID = be.BlogID;
            bcm.CategoryID = Convert.ToInt32(li.Value);

            be.BlogCategoryMappings.Add(bcm);
        }

        

        
        if (IsNew)
            db.BlogEntries.InsertOnSubmit(be);
        db.SubmitChanges();
        db.Dispose();

Why is this?

 

[Izi]

What I mean is that when you look at one objects properties, you shouldn't have go to through the linking table (i.e. it will looke like BlogEntry -> BlogCategory rather than BlogEntry -> MappingTable -> BlogCategory). If done correctly the mapping table does not appear on the DataContext at all - its all implicit.

Hi Goksly,

Thanks for that. Unfortunatly this isnt happing. How should I go about this? with the above diagram?

Thanks.

 

[Izi]

Firstly, the mapping table has to just be 2 columns... you can't have a primary key and then the 2 columns. Make them both primary keys (composite key). Then remove the 3 tables from your context, referesh server explorer and try again. When you drop the three of them on, only 2 should appear.

The one that got me when I was doing it was the fact it must be just 2 columns.

Hmm, my ORM still shows all three! Is this correct:

The above is my database diagram, not my ORM

 

[Izi]

any chance you could point me in the direction of a tutorial. i have tried searching, but my googling skills clearly aint up to scratch.

thanks for your help...

 

[Izi]

just fyi for anyone who reads this. this is a many to many relation ship so wont do what was being said above, i dont think.