CreateQuery using Entity Framework 5

Question

Trying to convert to EF 5 but having problems with CreateQuery. Any help would be much appreciated. Thank you in advance.

This is the code:

using System;
using System.ServiceModel;
using System.Data.Objects;
namespace TestingOneServiceRole
{
    public class Service1 : IService1
{
public void AddUser(string fullName, string userName, string password)
    {
        using (var context = new MyEntities())
        {
            context.AddToUser(new User()
            {
                UserName = userName,
                Password = password,
                FullName = fullName,
            });
            context.SaveChanges();
        }
    }
    public string UserLoginNow(string username, string password)
    {
        string query = @"SELECT value blah.FullName FROM MyEntities.blah AS User WHERE blah.UserName = @username AND blah.Password = @password";
        ObjectParameter[] parameters = new ObjectParameter[2];
        parameters[0] = new ObjectParameter("username", username);
        parameters[1] = new ObjectParameter("password", password);
        using (var context = MyEntities())
        {
            ObjectQuery<string> results = context.CreateQuery<string>(query, parameters);
            foreach (string result in results)
            {
 etc.......

I get an error that "TestingOneServiceRole.MyEntities" does not contain a definition for "CreateQuery" and no extension method... — Robin
– Robin, Commented Apr 26, 2013 at 16:05

Steven Magana-Zook · Accepted Answer · 2013-06-28 18:17:27Z

2

I assume in this response that your MyEntities inherits from DbContext in EF5.

Use the SqlQuery(of T) method in the DbContext.Database property to accomplish the same thing:

public string UserLoginNow(string username, string password)
{
    string query = @"SELECT value blah.FullName FROM MyEntities.blah AS User WHERE blah.UserName = @username AND blah.Password = @password";
    object[] parameters = new object[2];
    parameters[0] = username;
    parameters[1] = password;
    using (var context = MyEntities())
    {
        IEnumerable<string> results = context.Database.SqlQuery<string>(query, parameters);
        foreach (string result in results)
        {
        }
    }
}

If your entity framework model dervies from ObjectContext (as opposed to DbContext) then see this link provided by @MarkSowul on how to get access to the CreateQuery method in EF 5.

edited Jun 28, 2013 at 18:17

answered Apr 26, 2013 at 16:55

Steven Magana-Zook

2,77828 silver badges42 bronze badges

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

4 Comments

Mark Sowul Over a year ago

This is not composable. One workaround mentioned in the forums is to cast to IObjectContextAdapter (social.msdn.microsoft.com/Forums/en-US/…)

Steven Magana-Zook Over a year ago

@MarkSowul what do you mean by "composable"? Also, in my answer i did make the assumption,since the original code for the class backing the variable context was not provided, that the OP was deriving from DbContext. If the OP was using DbContext then my answer works fine (see msdn.microsoft.com/en-us/library/… and msdn.microsoft.com/en-us/library/…). If they are deriving from ObjectContext then your workaround works and i will add it to my answer.

Mark Sowul Over a year ago

By Composable I mean you can, for example, add a where clause and it will be filtered on the database, as opposed to in-memory. Sorry, entity framework really drives me nuts sometimes and I get cranky about it. I was on a fool's errand trying to get table-valued-functions to work, and ran into the dead-end of CreateQuery which people had suggested for EF4.

Philipp Munin Over a year ago

This does not work. Database.SqlQuery accept RAW sql, which will be executed on SQL server. SQL server does not know operand "Value" and it has no information about Entities container name (MyEntities), it expects schema name instead.

Hui Zhao · Accepted Answer · 2015-03-25 18:23:30Z

2

There are two steps:

One, add the reference to the class:

using System.Data.Entity.Infrastructure;

Two, rewrite your code as

var result= ((IObjectContextAdapter)context).ObjectContext.CreateQuery<blah_name>(query);

answered Mar 25, 2015 at 18:23

Hui Zhao

6752 gold badges12 silver badges21 bronze badges

Collectives™ on Stack Overflow

CreateQuery using Entity Framework 5

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related