Joseph  Norton

Joseph Norton

1548258843

How to get specific columns returned by stored procedure in variable/list using Entity Framework Core

I want to get list of specific columns returned by stored procedure.In the result set two column names will be common and one column name will be dynamic.

ALTER PROC DBO.GETLANGUAGETRANSLATION(@LANGCODE VARCHAR(10))
AS
BEGIN
DECLARE @QUERY NVARCHAR(255)
SET @QUERY= N'SELECT RESOURCENAME,ENText,'+UPPER(@LANGCODE)+' INTO #TEMP FROM DBO.LANGUAGETRANSLATION SELECT * FROM #TEMP';
EXEC(@QUERY);
END

RESOURCENAME and ENText column will be same in output each time but based in input parameter third column name will vary.

I am writing below code in C# using Entity Framework Core LINQ

public async Task<IEnumerable<LanguageTranslation>> GetAsync(string langCode)
        {
            var context = new LQMSDbContext(AppConstants.DB_CONNECTION_STRING_KEY);
            try
            {
                string query = "GetLanguageTranslation '" + langCode + "'";
                var result = context.LanguageTranslation.FromSql(query).ToList();
                var result1 = context.Database.ExecuteSqlCommand("GETLANGUAGETRANSLATION @p0", parameters: langCode );
            }
            catch(Exception ex)
            {
                string a = ex.Message;
            }
        return await _dbSet
        .Where(x =&gt; x.Equals(langCode))
         .ToListAsync();
    }

I am trying to call stored procedure GETLANGUAGETRANSLATION using two different approaches. But both are failing with below error

The required column ‘ESText’ was not present in the results of a ‘FromSql’ operation

Where ESText refers to the column which I am not returning from stored procedure but present in table.

I want to store only few columns in result set in c# and not all.

Can any body help me in this?

NOTE : It works fine with Select * from LanguageTranslation query


#entity-framework #c-sharp

What is GEEK

Buddha Community

Elthel Mario

1548644327

Have a look at how to execute raw queries. https://docs.microsoft.com/en-us/ef/core/querying/raw-sql

You can write the query to store the results temporarily in a temp table then select from that table.

FromSql takes more parameters for your injection, you will want to use those extra parameters to place yours into the query. Don’t string concatenate.

Another note: when you use ToList or ToArray or Count you will execute the query on the server.

Einar  Hintz

Einar Hintz

1602560783

jQuery Ajax CRUD in ASP.NET Core MVC with Modal Popup

In this article, we’ll discuss how to use jQuery Ajax for ASP.NET Core MVC CRUD Operations using Bootstrap Modal. With jQuery Ajax, we can make HTTP request to controller action methods without reloading the entire page, like a single page application.

To demonstrate CRUD operations – insert, update, delete and retrieve, the project will be dealing with details of a normal bank transaction. GitHub repository for this demo project : https://bit.ly/33KTJAu.

Sub-topics discussed :

  • Form design for insert and update operation.
  • Display forms in modal popup dialog.
  • Form post using jQuery Ajax.
  • Implement MVC CRUD operations with jQuery Ajax.
  • Loading spinner in .NET Core MVC.
  • Prevent direct access to MVC action method.

Create ASP.NET Core MVC Project

In Visual Studio 2019, Go to File > New > Project (Ctrl + Shift + N).

From new project window, Select Asp.Net Core Web Application_._

Image showing how to create ASP.NET Core Web API project in Visual Studio.

Once you provide the project name and location. Select Web Application(Model-View-Controller) and uncheck HTTPS Configuration. Above steps will create a brand new ASP.NET Core MVC project.

Showing project template selection for .NET Core MVC.

Setup a Database

Let’s create a database for this application using Entity Framework Core. For that we’ve to install corresponding NuGet Packages. Right click on project from solution explorer, select Manage NuGet Packages_,_ From browse tab, install following 3 packages.

Showing list of NuGet Packages for Entity Framework Core

Now let’s define DB model class file – /Models/TransactionModel.cs.

public class TransactionModel
{
    [Key]
    public int TransactionId { get; set; }

    [Column(TypeName ="nvarchar(12)")]
    [DisplayName("Account Number")]
    [Required(ErrorMessage ="This Field is required.")]
    [MaxLength(12,ErrorMessage ="Maximum 12 characters only")]
    public string AccountNumber { get; set; }

    [Column(TypeName ="nvarchar(100)")]
    [DisplayName("Beneficiary Name")]
    [Required(ErrorMessage = "This Field is required.")]
    public string BeneficiaryName { get; set; }

    [Column(TypeName ="nvarchar(100)")]
    [DisplayName("Bank Name")]
    [Required(ErrorMessage = "This Field is required.")]
    public string BankName { get; set; }

    [Column(TypeName ="nvarchar(11)")]
    [DisplayName("SWIFT Code")]
    [Required(ErrorMessage = "This Field is required.")]
    [MaxLength(11)]
    public string SWIFTCode { get; set; }

    [DisplayName("Amount")]
    [Required(ErrorMessage = "This Field is required.")]
    public int Amount { get; set; }

    [DisplayFormat(DataFormatString = "{0:MM/dd/yyyy}")]
    public DateTime Date { get; set; }
}

C#Copy

Here we’ve defined model properties for the transaction with proper validation. Now let’s define  DbContextclass for EF Core.

#asp.net core article #asp.net core #add loading spinner in asp.net core #asp.net core crud without reloading #asp.net core jquery ajax form #asp.net core modal dialog #asp.net core mvc crud using jquery ajax #asp.net core mvc with jquery and ajax #asp.net core popup window #bootstrap modal popup in asp.net core mvc. bootstrap modal popup in asp.net core #delete and viewall in asp.net core #jquery ajax - insert #jquery ajax form post #modal popup dialog in asp.net core #no direct access action method #update #validation in modal popup

Joseph  Norton

Joseph Norton

1548258843

How to get specific columns returned by stored procedure in variable/list using Entity Framework Core

I want to get list of specific columns returned by stored procedure.In the result set two column names will be common and one column name will be dynamic.

ALTER PROC DBO.GETLANGUAGETRANSLATION(@LANGCODE VARCHAR(10))
AS
BEGIN
DECLARE @QUERY NVARCHAR(255)
SET @QUERY= N'SELECT RESOURCENAME,ENText,'+UPPER(@LANGCODE)+' INTO #TEMP FROM DBO.LANGUAGETRANSLATION SELECT * FROM #TEMP';
EXEC(@QUERY);
END

RESOURCENAME and ENText column will be same in output each time but based in input parameter third column name will vary.

I am writing below code in C# using Entity Framework Core LINQ

public async Task<IEnumerable<LanguageTranslation>> GetAsync(string langCode)
        {
            var context = new LQMSDbContext(AppConstants.DB_CONNECTION_STRING_KEY);
            try
            {
                string query = "GetLanguageTranslation '" + langCode + "'";
                var result = context.LanguageTranslation.FromSql(query).ToList();
                var result1 = context.Database.ExecuteSqlCommand("GETLANGUAGETRANSLATION @p0", parameters: langCode );
            }
            catch(Exception ex)
            {
                string a = ex.Message;
            }
        return await _dbSet
        .Where(x =&gt; x.Equals(langCode))
         .ToListAsync();
    }

I am trying to call stored procedure GETLANGUAGETRANSLATION using two different approaches. But both are failing with below error

The required column ‘ESText’ was not present in the results of a ‘FromSql’ operation

Where ESText refers to the column which I am not returning from stored procedure but present in table.

I want to store only few columns in result set in c# and not all.

Can any body help me in this?

NOTE : It works fine with Select * from LanguageTranslation query


#entity-framework #c-sharp

Hertha  Mayer

Hertha Mayer

1602668764

Announcing Entity Framework Core (EF Core) 5 RC2

Today, the Entity Framework Core team announces the second release candidate (RC2) of EF Core 5.0. This is a feature complete release candidate of EF Core 5.0 and ships with a “go live” license. You are supported using it in production. This is a great opportunity to start using EF Core 5.0 early while there is still time to fix remaining issues. We’re looking for reports of any remaining critical bugs that should be fixed before the final release.

Prerequisites

EF Core 5.0 will not run on .NET Standard 2.0 platforms, including .NET Framework.

How to get EF Core 5.0 Release Candidate 2

EF Core is distributed exclusively as a set of NuGet packages. For example, to add the SQL Server provider to your project, you can use the following command using the dotnet tool:

dotnet add package Microsoft.EntityFrameworkCore.SqlServer --version 5.0.0-rc.2.20475.6

#.net #.net core #.net framework #asp.net #c# #entity framework #announcement #asp.net core #entity framework core

Ken  Mueller

Ken Mueller

1595261580

Entity Framework Core in ASP.NET Core 3.1

This article will be covering in detail the implementation of Entity Framework Core in ASP.NET Core Web API. We will learn about entity framework core in details i.e. what, how & why and see how to use it in ASP.NET Core Web API to implement CRUD operations. Though here we will be using entity framework in ASP.NET Core this can be implemented even in .NET Framework for your data persistence. I hope you will join me on this and go through step by step demonstration on implementing entity framework core in ASP.NET Core Web API.

Introduction to Entity Framework Core

ASP.NET Core Entity Framework

Entity Framework has been around since 2008 which has improved over the years. Entity framework core was introduced for .NET Core based applications. It is an open-source object-relational mapper (ORM) built over ADO.NET and an official data access platform from Microsoft. Entity framework core has been rewritten from scratch to make it lightweight, flexible & achieve better performance.

In the absence of an ORM typically lots of code have to be written to perform CRUD operations with the database. using an ORM like entity framework can reduce the number of lines of code to be written for database interactions. Typical ORM will work on classes & database tables of similar structure but entity framework core implements mappings which makes it a lot more flexible i.e. in case table & class does not match then mappings can be exercised to get from objects to tables.

Entity framework core can work with wide variety of databases. Microsoft provides rich providers for SQL Server & SQLite and there are many other third party providers some commercial and some from community.

How does entity framework core works?

How Entity Framework Works

  1. Define domain classes & configure entity framework DbContext API along with mappings to let entity framework work how this class maps to the database schema.
  2. Write LINQ to objects to fetch data from the database.
  3. Based on mappings & database provider configured entity framework will translate queries into SQL that’s understood by the database.
  4. The query is executed and the object is populated with data.
  5. Data in objects can be modified, deleted or new records can be added to the list & once done call DbContext.saveChanges from EF API to save data back to the database.
  6. The entity framework will generate the required SQL statements to save data back to the database.

#programming #.net core #asp.net core 3.1 #entity framework #entity framework core

Get an Object from database using Asp.Net Core MVC 5 with Entity Framework Core

https://youtu.be/YEj00ltSyO8

#repository pattern #asp.net core mvc 5 #entity framework core #core #mvc #asp.net