Convert MySQL query in to Entity Framework query syntax

Question

Please can somebody help me convert this MySQL query in to Entity Framework? Im using that in an ASP .NET project but I only know how to do basic queries in Entity Framework. This seems way too complicated and I cant find a working solution since many hours now...

 $rs = mysql_query("SELECT barcode,latitude,longitude,date_time,location_name 
                    FROM sDetails
                    JOIN users on (users.id=sDetails.UserId)  
                    WHERE  (latitude and longitude is not null) 
                       and (latitude <> 100000 and longitude <> 100000)
               and id='$UserId'
            ORDER BY date_time desc");

Phil · Accepted Answer · 2012-03-08 07:07:44Z

1

Assuming you have generated an Entity Framework model from the database you should be able to do something like (c# example):

 var q = context.Details.Where( d => d.UserId = "$UserID" ).
             Where( d => d.latitude.HasValue && d.latitude != 100000 && .. etc ).
             OrderByDescending( d => d.date_time ).
             Select( d => new {d.barcode, d.latitude, d.longitude, etc ... };

I'm not sure why you have a join since you're not selecting anything from the users tables.

answered Mar 8, 2012 at 7:07

Phil

43.2k9 gold badges102 silver badges102 bronze badges

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

1 Comment

Altin Over a year ago

thanks for the accurate answer. Please look at my answer below. I'm still having some error with that..

Altin · Accepted Answer · 2012-03-08 16:32:46Z

0

Thanks @Phil the code you gave above, seems to work mostly, but it still shows an error at var q = context.ScanDetails.Where

Error 3: 'System.Web.HttpContext' does not contain a definition for 'ScanDetails' and no extension method 'ScanDetails' accepting a first argument of type 'System.Web.HttpContext' could be found (are you missing a using directive or an assembly reference?)

this is the code I have so far:

public class read : IHttpHandler
{
    sqlEntities _db = new sqlEntities();

    public bool IsReusable { get { return true; } }

    public void ProcessRequest(HttpContext context)
    {
        //Write out the JSON you want to return.

       var q = context.ScanDetails.Where( d => d.UserId = "1" ).
         Where( d => d.latitude.HasValue && d.latitude != 100000  ).
         OrderByDescending( d => d.date_time ).
         Select( d => new {d.barcode, d.latitude, d.longitude };


           // stuff to do here

    }


    public IQueryable<ScanDetail> scanData { get; set; }
}

answered Mar 8, 2012 at 16:32

Altin

2,2655 gold badges29 silver badges50 bronze badges

2 Comments

Phil Over a year ago

when I put in context I meant the database context, in your case _db. Your supposed to amend your question, not add an answer (unless it is an answer of course).

Altin Over a year ago

thanks @Phil I accepted your answer as the solution. Also I will take in to consideration your suggestion about answering in the future. Have a great time :)

Collectives™ on Stack Overflow

Convert MySQL query in to Entity Framework query syntax

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related