LINQ ASP .net mvc actionresult CREATE

Question

This is my code of the ActionResult Create method:

public ActionResult Create([Bind(Include = "FileStatusID, Name, MinValue, MaxValue")] fileStatusModel FILeStatusModel, TBL_FileStatus tBL_FileStatus) //include tem os valores que vamos inserir na view
{
    var userID = ((SessionModel)Session["SessionModel"]).UserID; // get current user id
    using (var query = new Core_DBEntities)
    {
        var query1 = ??????????????
    }
    if (ModelState.IsValid)
    {
        TBL_FileStatus item = new TBL_FileStatus()
        {
            Name = FILeStatusModel.Name,
            MinValue = FILeStatusModel.MinValue,
            MaxValue = FILeStatusModel.MaxValue,
            Ative = true,
            CreateDate = DateTime.Now,
            CreateBy = userID
        };

        db.TBL_FileStatus.Add(item);
        db.SaveChanges();
        return RedirectToAction("Index");
    }
    return View();
}

And this is my SQL query:

DECLARE @p_valuemin INT
DECLARE @p_valuemax INT

SET @p_valuemin = 9   
SET @p_valuemax = 183

IF @p_valuemin >= @p_valuemax
    RAISERROR('@p_valuemin deve ser menor que o @p_valuemax', 16, 1)

SELECT *
FROM [TBL_FileStatus]
WHERE Ative = 1 AND ((@p_valuemin BETWEEN MinValue AND MaxValue) AND MaxValue <> @p_valuemin)
OR ((@p_valuemax BETWEEN MinValue AND MaxValue) AND MinValue <> @p_valuemax)
UNION
SELECT *
FROM [TBL_FileStatus]
WHERE Ative = 1 AND ((MinValue BETWEEN @p_valuemin AND @p_valuemax) AND MinValue <> @p_valuemax)
OR ((MaxValue BETWEEN @p_valuemin AND @p_valuemax) AND MaxValue <> @p_valuemin)

those 2 values will represent the textbox of create
SET @p_valuemin = 9   
SET @p_valuemax = 183

My question is:

I want to join this SQL query with the ActionResult Create, to make a condition before clicking on the create button.

How can i achieve this?

Answer 1

If you require to call a stored procedure then Entity Framework has the SqlQuery() method.

You can call your stored procedure like this:

var p_valuemin = new SqlParameter
                     {
                         ParameterName = "p_valuemin",
                         Value = 9
                     };
var p_valuemax = new SqlParameter
                     {
                         ParameterName = "p_valuemax",
                         Value = 183
                     };

var query = "GetFileStatus @p_valuemin, @p_valuemax";

var result = db.Database.SqlQuery<GetFileStatus>(query, p_valuemin, p_valuemax).ToList();

Here p_valuemin and p_valuemax are your SQL Server stored procedure parameters, and as mentioned in query GetFileStatus is your stored procedure name and GetFileStatus is your result model like your query returned result entity from the stored procedure

db.Database.SqlQuery<GetFileStatus>(query,p_valuemin, p_valuemax).ToList(); 

for calling a stored procedure.

Your result GetFileStatus model looks like

public class GetFileStatus
{
    public int FileStatusID { get; set; }
    public string Name { get; set; }
    public int MinValue { get; set; }
    public int MaxValue { get; set; }
    public bool Active { get; set; }
}

I hope it helps you and let me know if require any more information.