Running direct SQL query from Entity Framework

First we will create a test table in our database

CREATE TABLE [dbo].[Address](

                [Id] [int] IDENTITY(1,1) NOT NUL primary keyL,

                [AddressLine1] [varchar](100) NULL,

                [AddressLine2] [varchar](100) NULL,

                [City] [varchar](100) NULL,

                [ZipCode] [varchar](15) NULL,

                [Phone] [varchar](15) NULL,

                [Mobile] [varchar](15) NULL

)

Create a console application ,Add Ado.Net entity model for database in which we have just created test table,while selecting tables make sure Address table is selected.

In my case database was Playground & context class generated is  PlaygroundEntities.

Inside your main we will add our code that will do native query on top of context class using ExecuteStoreQuery

Here is my code inside main

static void Main(string[] args)

        {

            PlaygroundEntities db = new PlaygroundEntities();

            //var query = from m in db.Addresses where m.City == "kankavali" select m;

           

            object[] parameters = { "1"};

            ObjectResult<ShortAddress> ads = db.ExecuteStoreQuery<ShortAddress>("select Id,AddressLine1 from Address where id= {0}", parameters);

            foreach (ShortAddress i in ads)

            {

                Console.WriteLine(i.AddressLine1);

                Console.WriteLine();

            }

           

            Console.ReadKey();

        }

As in select I am taking only 2 columns and address entity has more column we need to have one more class of whose type our result is.

That’s why we will add a simple class as follows

class ShortAddress

    {

        public int Id { get; set; }

        public string AddressLine1 { get; set; }

    }

time to insert some test records in Address table. Now we are ready to run console application and check output