How to convert this complex Entity Framework query into a standard SQL query? What exactly perform?

Question

I have no experience with .NET Entity Framework and I have to understand what exactly do this Entitity Framework query and translate in in classic SQL (for Microsoft SQL server)

So I have this query:

using (MyCorp.EarlyWarnings.Model.EarlyWarningsEntities context = new Model.EarlyWarningsEntities())
            {
                DBContext.SetTimeout(context);
float vulnerabilities_thisweek = (from x in context.VulnerabilityAlertDocuments
                                .Select(p => new { p.Id, p.Published, p.Title, p.Severity, p.LastUpdated })
                                .AsEnumerable()
                                .Select(p => new MyCorp.EarlyWarnings.Model.VulnerabilityAlertDocument()
                                {
                                    Id = p.Id,
                                    Published = p.Published,
                                    Title = p.Title,
                                    Severity = p.Severity,
                                    LastUpdated = p.LastUpdated
                                })
                                .Where(p => p.LastUpdated.Date > DateTime.Now.AddDays(-7).Date)
                                                  select x).Count();
}

I know that the result of this query on my database is -1,00 (I know this information executing this query)

I also know that this query work on a single table (or at least I think it is so, correct if I am saying wrong thing) because I have from x in context.VulnerabilityAlertDocuments and in the EarlyWarningsEntities class I found this property that map the Data Base table named as VulnerabilityAlertDocument on the DbSet object named VulnerabilityAlertDocuments:

public DbSet<VulnerabilityAlertDocument> VulnerabilityAlertDocuments { get; set; }

So I think that my SQL query have to work only on the VulnerabilityAlertDocument database table.

I can't understand what exactly do when in the where condition use the => "operator", I have try to search on Google but I can't find nothing

I also have some difficulties to understand what exactly do the AsEnumerable() method called on the .Select() result.

Reading the documentation it seems to me that the first .Select() method return to me a set of rows and then it is called the AsEnumerable() method to have an iterator on this collection but I am not sure, do you confirm or is it wrong?

Then it execute a second select and I can't understand on what (it seems to me that the table is the same)

Someone can help me to understand what exactly do this query and how can I convert it in standard SQL?

Tnx

E-Bat · Accepted Answer · 2014-03-21 17:25:54Z

1

Your query is creating an anonimous type just for nothing, i have not tested but i think something like the following will produce the same results:

float vulnerabilities_thisweek = (from x in context.VulnerabilityAlertDocuments                                    
                                 .Where(p => p.LastUpdated > DateTime.Now.AddDays(-7))
                                 .Count();
}

also i recomend you to take a look to LinqPad, you can test your linq queries and see the corresponding sql statements

answered Mar 21, 2014 at 17:25

E-Bat

4,9121 gold badge36 silver badges58 bronze badges

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

4 Comments

AndreaNobili Over a year ago

are you saying to me that LinkPad can show me the SQL query converion of the Entity framework query?

E-Bat Over a year ago

Exactly, it is very easy to setup, i recommend you to download the portable version.

AndreaNobili Over a year ago

O, I have downloaded it but now what have I to do to translate my Entity framework query to SQL query using LinkPad? Have I first to create a DB connection? and then?

E-Bat Over a year ago

Just execute the query, in the bottom panel you can switch between Result pane, Sql Lamdda and IL. Put the code i post you and see what you get. You have to select "c# Statements" in this case.

Nasir · Accepted Answer · 2014-03-21 17:19:31Z

0

This seems to be overly complicated for a COUNT query..

From what I see, this can translate into:

SELECT COUNT(*) FROM VulnerabilityAlertDocuments WHERE DateDiff(day, LastUpdated,  GetDate()) < 8

answered Mar 21, 2014 at 17:19

Nasir

11.5k9 gold badges34 silver badges40 bronze badges

2 Comments

AndreaNobili Over a year ago

mmm can you explain to me what exactly do my query and how you convert it into your version?

Nasir Over a year ago

apologies, had to step away from my desk. In regards to how I converted it, I walked backwards. .Count & the .Where. Everything above those is extraneous. As far as what your query is doing, it is selecting certain fields, creating an anonymous type, then recreating VulnerabilityAlertDocument with only the selected fields from the anonymous type, then applying the Where function and then counting it. Most of it is not needed.

Collectives™ on Stack Overflow

How to convert this complex Entity Framework query into a standard SQL query? What exactly perform?

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related