How do I add data to column from another table in a SQL Server using .NET Framework?

Question

I have 2 tables in my SQL Server. They have a foreign key relationship. I now want to add a new record to the table with the index. For this I want to use the ID of the primary key. I don't know how to write the statement in my C# to add this ID to the record.

I already tried some stuff, but because I'm new to this topic I don't how much sense it makes ^^

e.g.

ArticleId = db.TestArtikel.Where(x => x.Name == "test1")

public static void AnotherFunction()
{
    using (DbEntities db = new DbEntities())
    {
         db.TestProduct.Add(new TestProduct()
            {
                ProductId = Guid.NewGuid(),
                Price = 10,
                DeliveryDate = DateTime.Now,
                ArticleId = 
            });

         db.SaveChanges();
     }
}

When I use the the line above the code for the ArticleId, it throws the error:

Cannot implicitly convert type 'System.Linq.IQueryable' to 'System.Guid'

The error is because your Linq where clause doesn't know it's only returning 1 item. There could be multiple items that match "test1". You should be able to add '.FirstOrDefault()' on the end of that statement to get it work. — sr28
– sr28, Commented Aug 2, 2019 at 7:39

Matt Ghafouri · Accepted Answer · 2019-08-02 08:12:44Z

1

You just need to edit the first query with the below statement and use the ArticleId in add process.

Article = db.TestArtikel.Where(x => x.Name == "test1").SingleOrDefault();


public static void AnotherFunction()
{
 using (DbEntities db = new DbEntities())
 {
     db.TestProduct.Add(new TestProduct()
        {
            ProductId = Guid.NewGuid(),
            Price = 10,
            DeliveryDate = DateTime.Now,
            ArticleId = Article.ArticleId
        });

     db.SaveChanges();
  }
}

The point you should notice it, the Article maybe be null, to prevent the exception you would better to use

Article = db.TestArtikel.Where(x => x.Name == "test1").Single();

Or write a validation statement before adding :

if(Article == null)  //do somthing else

answered Aug 2, 2019 at 8:12

Matt Ghafouri

1,5972 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Erkmen Esen · Accepted Answer · 2019-08-02 08:03:07Z

0

Your Where clause don't know how many items that it returns. Try this;

ArticleId = db.TestArtikel.Where(x => x.Name == "test1").FirstOrDefault();

or

ArticleId = db.TestArtikel.Where(x => x.Name == "test1").SingleOrDefault();

it depends your purpose or data.

answered Aug 2, 2019 at 8:03

Erkmen Esen

262 bronze badges

1 Comment

Inagnikai Over a year ago

You can shorten both of these expressions by removing the call to .Where and putting the same lambda in the FirstOrDefault/SingleOrDefault

Collectives™ on Stack Overflow

How do I add data to column from another table in a SQL Server using .NET Framework?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related