Moving from SQL to Entity Framework Code First

Question

I am making the journey from an SQL centric world (where I am reasonably fluent) to EF dDbContect with code first and I am struggling. The following is a simplified example; I want to write the following simple SQL query (which took 60 seconds to write):

SELECT
    HP.HospitalID
FROM
    Hospitals AS HP
    JOIN NHSTrusts AS NT
    ON NT.NHSTrustID = HP.NHSTrust_NHSTrustID

WHERE
    HP.HospitalName + ', ' + NT.NHSTrustName = 'My hospital name including trust'

as an EF style query. I cannot see how to do this and I don't want to drop back into SQL everytime I cannot see how to do something.

Can anyone help:

On how to frame the above query in EF dbContent
On a general source of help with

please note that WHERE HP.HospitalName='hospital name' AND NT.NHSTrustName = 'trust' could potentially be more efficient. Your approach excludes the usage of indexes — Cristian Lupascu
– Cristian Lupascu, Commented Oct 4, 2013 at 9:07
Each trust as a collection of hospitals. A hospital can only belong to one trust. The foreign key is HP.NHSTrust_NHSTrustID — Peter Smith
– Peter Smith, Commented Oct 4, 2013 at 9:10
Hi W0lf and thanks . The SQL query was just knocked up quickly as an example; translation is the problem, not efficiency. — Peter Smith
– Peter Smith, Commented Oct 4, 2013 at 9:12

Cristian Lupascu · Accepted Answer · 2013-10-04 09:17:00Z

3

Assuming your entities and DB Context are properly set, here's how your query may look like:

var hospitalIds = 
    from hp in dbContext.Hospitals
    where 
        hp.HospitalName == "..." &&
        hp.Trust.NHSTrustName == "..."
    select hp.HospitalId;

Of course, this query will have to be materialized by iterating through the results (or by using .ToList()/.ToArray()).

To define the entities and context properly, refer to this excellent tutorial on MSDN: http://msdn.microsoft.com/en-us/data/jj193542.

answered Oct 4, 2013 at 9:17

Cristian Lupascu

40.8k18 gold badges107 silver badges139 bronze badges

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

1 Comment

Kristof Over a year ago

I totally support this! If your relationships are set up properly you can make use of those relationship properties to create a very readable query.

Dennis · Accepted Answer · 2013-10-04 09:30:33Z

2

Assuming these entity types:

public class Trust
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<Hospital> Hospitals { get; set; }
}

public class Hospital
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int TrustId { get; set; }
    public Trust Trust { get; set; }
}

you'll have this query:

dbContext
    .Hospitals
    .Where(h => h.Name + h.Trust.Name == "My hospital name including trust")
    .Select(h => h.Id);

edited Oct 4, 2013 at 9:30

answered Oct 4, 2013 at 9:22

Dennis

37.9k11 gold badges87 silver badges158 bronze badges

2 Comments

Peter Smith Over a year ago

Hi Dennis, many thanks for this. A supplementary question; can any LINQ query also be written using lamda expressions.

Dennis Over a year ago

@PeterSmith: these two ways are called "query syntax" and "method syntax" (my sample). You can start here: msdn.microsoft.com/en-us/library/vstudio/bb397947.aspx. In most cases, the choice between them is a question of personal preferences.

user240141 · Accepted Answer · 2013-10-04 09:11:35Z

1

First try some Linq. You can write it as below. The query might have some mistake, it will be corrected by Intellisense.

var s  = (from c in Context.Hospitals
          from h in Context.NHSTrusts
          where c.NHSTrust_NHSTrustID==h.NHSTrustID 
          && string.Format("{0},{1}",c.HospitalName,h.NHSTrustName).Equals("My hospital name including trust")

          select c.HospitalId).FirstOrDefault();

answered Oct 4, 2013 at 9:11

user240141

3 Comments

Kristof Over a year ago

You would've received my vote if you had used a join statement rather than 2 froms :)

Peter Smith Over a year ago

Hi Amit, that's great. I was using lamda expressions. I really need to read up on LINQ.

Dennis Over a year ago

What is the need for two from statements (or even join), if OP's entities should be related with the foreign key association?

Kristof · Accepted Answer · 2013-10-04 09:15:15Z

0

For a good place to get some examples i would recommend 101 linq samples
It has everything from using a WHERE to GROUP BY statements.

answered Oct 4, 2013 at 9:15

Kristof

3,3351 gold badge22 silver badges31 bronze badges

Collectives™ on Stack Overflow

Moving from SQL to Entity Framework Code First

4 Answers 4

1 Comment

2 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related