How to write web API make by entity framework by ADO.NET ?

Question

problem How to write web API make by entity framework by ado.net ? I work on SQL server 2012 with asp.net core 2 . I need to rewrite web API below but with ado.net so that how to make that ? database blow : CREATE TABLE [dbo].[ User ]( [Id] [ int ] IDENTITY(1,1) NOT NULL , [FirstName] [nvarchar](250) NULL , [LastName] [nvarchar](250) NULL , [Email] [nvarchar](250) NULL , [Phone] [nvarchar](50) NULL , CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED ( [Id] ASC ) WITH (PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , _ ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ] ) ON [ PRIMARY ] GO /****** Object: Table [dbo].[UserMarks] Script Date : 8/15/2018 9:22:00 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[UserMarks]( [id] [ int ] NOT NULL , [userId] [ int ] NULL , [mark] [ decimal ](18, 2) NULL , CONSTRAINT [PK_UserMarks] PRIMARY KEY CLUSTERED ( [id] ASC ) WITH (PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , _ ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ] ) ON [ PRIMARY ] What I have tried: [HttpGet, Route( "GetResult" )] public async Task > GetResult() { List query = null ; try { using (_ctx) { query = await (from um in _ctx.UserMarks join m in _ctx.User on um.UserId equals m.Id select new vmMarks { mid = ( int )m.Id, mName = m.FirstName + " " + m.LastName, mMark = ( decimal )um.Mark }).OrderByDescending(x => x.mMark).ToListAsync(); } } catch (Exception ex) { ex.ToString(); } return query; }

Jose Hernandez · Answer

your Linq query is translated in transact sql as follows: "select u.Id as 'mid', 'mName' = u.FirstName + ' ' + u.LastName, 'mMark' = um.mark from UserMarks um inner join User u on um.userId = u.Id order by mMark desc" ; and here is the translate of the full function using sqlcommand and data reader: List ListItems = new List (); // your list of vmMarks items using (SqlConnection connection = new SqlConnection( "yourConnectionString" )) { connection.Open(); string queryString = "select u.Id as 'mid', 'mName' = u.FirstName + ' ' + u.LastName, 'mMark' = um.mark from UserMarks um inner join User u on um.userId = u.Id order by mMark desc" ; SqlCommand command = new SqlCommand(queryString, connection); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { //Create new Item and add it to the list vmMarks vm = new vmMarks(); vm.mid = reader[0]; //get first column vm.mName = reader[1]; //get second column vm.mMark = reader[2]; //get third column ListItems.add(vm); } } return ListItems; inside the while(reader.read()) statement you might need to parse the columns to the same type of the properties of your model, i.e.: mid model property is an int32 then you can parse column as: vm.mid = Convert.Toint32(reader[0].ToString())

ahmed elbarbary · Answer

can you help me i need rewrite api i do with ado.net using sql command and data reader so how can i do that

Jose Hernandez · Answer

Not sure why you want a async function but for the linq statement you can try the following: [HttpGet, Route( "GetResult" )] public List GetResult() { using (context _ctx = new context()) { List items = (from um in _ctx.UserMarks join m in _ctx.User on um.UserId equals m.Id order by um.mMark descending select new vmMarks { mid = ( int )m.Id, mName = m.FirstName + " " + m.LastName, mMark = ( decimal )um.Mark }).ToList(); return items; } } hope that helps.