Execute SQL Query in a controller (Entity Framework)

Question

I'm trying to execute some SQL query from my controller action that takes in the VALES () Parameters based on ids in the view, some code to better explain:

My Controller, based on what i found online :

    public ActionResult Add(int id)
    {

        Entitiesdb db = new Entitiesdb();//not sure if this be deck or cards? 
        string query = $"INSERT INTO DeckCards (Card_Id, Deck_id) Values ({cardId},{deckId});";
        db.Database.ExecuteSqlCommand(query, @id);

        return View("Index");
    }

My View button that calls the Add Method in the controller:

@foreach (var d in Model.Decks)
{
<ul class="nav navbar-nav navbar-right">
    @Html.ActionLink($"Add to: {d.Name} ", "Add", new { cardId = Model.Cards.Id, deckId = d.id })
</ul>
}

My models:

 public class Deck
    {
        public int id { get; set; }

        public string Name { get; set; }

        public string Notes { get; set; }
        [DisplayName("Card")]
        public virtual List<Card> Card { get; set; }
    }

public class Card
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int? Atk { get; set; }
    public int? Def { get; set; }
    public string Desc {get; set;}
    public int? Level { get; set; }
    public string Type { get; set; }
    public string Attribute { get; set; }
    [DisplayName("Image")]
    public virtual List<Image> Card_Images { get; set; }

    public virtual List<Deck> Deck { get; set; }


}

Why don't you use the Entity Framework capabilities - seeing that you've already referenced it? Create an object of type DeckCards, set its properties, then save it using the EF DbContext.SaveChanges() method....... what's the point of having EF included, and then using "raw" SQL to do stuff?!?! — marc_s
– marc_s, Commented Sep 18, 2019 at 10:54
haha sorry! 'Cannot resolve symbol EntitiesDB. I am not sure to what this is referring to, found this on a stack overflow post and it wasnt explained — dros
– dros, Commented Sep 18, 2019 at 10:55
@marc_s, i'm very new at entity and not very sure of how to approach what you are recommending, could you provide a snippet to help me understand? — dros
– dros, Commented Sep 18, 2019 at 10:57
@also its worth mentioning that there isnt a DeckCard Model, this is a relationship formed in my DB two seperate Deck and Card models. Im confused at to what ntitiesdb db = new Entitiesdb(); refers to in the snippet i sent over — dros
– dros, Commented Sep 18, 2019 at 11:00

Divyesh patel · Accepted Answer · 2019-09-18 11:42:31Z

1

From your scenario I assume that you may at least have 3 table.

1).Card
2).Desk
3).DeckCard

So In your controller:

public ActionResult Add(int cardId,int deckId)
{
    using (YourSolution.YoueProjectName.Models.EntitiesDB db=new EntitiesDB())
    {
     DeskCard d=new DeskCard();
     d.Card_Id=cardId;
     d.Deck_Id=deckId;    
     db.DeckCard.Add(d)//Or AddObject() basesd on version
     db.SaveChanges();
     }
   }
}

edited Sep 18, 2019 at 11:42

answered Sep 18, 2019 at 11:36

Divyesh patel

1,0071 gold badge9 silver badges21 bronze badges

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

6 Comments

dros Over a year ago

You are indeed correct, I have 3 Tables, but only 2 models (Deck, Card) but not a DeckCard Model. Looks like I will need to create one?

Divyesh patel Over a year ago

in INSERT INTO DeckCards (Card_Id, Deck_id) Values ({cardId},{deckId}); what is DeckCards ??

dros Over a year ago

DeckCards is a table created via the relationship between my two models, look at my edit for info on the two models

Daniel Schmid Over a year ago

So it's two models referring to the same DB table?

Daniel Schmid Over a year ago

oh yes obviously it's the intermediate table because card and deck have many-to-many relationship. In this case, EntitiesDb refers to the two models. The variable db will have the two properties db.Cards and db.Decks which allow you to select, modify and delete data. I strongly recommend you to understand the concepts of EF learn.microsoft.com/ef

|

Collectives™ on Stack Overflow

Execute SQL Query in a controller (Entity Framework)

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related