The following are examples of SQL queries and how to execute them with Entity Framework

SELECT * with Entity Framework

SELECT * FROM Contacts

var mydbContext.Contacts.ToList();

SELECT Id with Entity Framework

This is really for how to select any single column.

SELECT Id FROM Contacts

List<int> idList = dbContext.Contacts.Select( c => c.Id ).ToList(); // Assuming Id is of type int

SELECT only one row with Entity Framework

This is really for how to select any single row from a table.

SELECT * FROM Contacts where Id = 10

var id = 10;
var contact = dbContext.Contacts.SingleOrDefault( c => c.Id = id );

SELECT only one result with Entity Framework

This is really for how to select any single value from a single row in a table.

SELECT UserId FROM Contacts where Id = 10

var id = 10;
int userId = dbContext.Contacts.Where( c => c.Id = id ).Select( c => c.UserId ).SingleOrDefault();

SELECT first result with Entity Framework

This is really for how to select the first value from a list of returned rows from a table.

SELECT TOP 1 * FROM Contacts

var id = 10;
int userId = dbContext.Contacts.FirstOrDefault();

INSERT INTO with Entity Framework

INSERT INTO dbo.LD_User (Name, Type, Active, CreateDate, CreatedBy)
VALUES ('user1', 1, 1, GetDate(), 101)

var user = new User { Name = "user1", Type = UserType.Contact, true, DateTime.Now, User.System };
dbContext.Users.Add(user);
dbContext.SaveChanges();

Note: UserType.Contact and User.System are enums in the above example.

INSERT INTO … SELECT with Entity Framework

Imagine you have these three tables. Product, ProductGroup, ProductGroupMembership. You want to make Products a member of a group by inserting into the ProductGroupMembership table.

INSERT INTO ProductGroupMembership (ProductId, GroupId, CreateDate, CreatedBy)
SELECT Id, @GroupId, GetDate(), @CreatedBy FROM Product
WHERE Id IN (1, 2, 3, 4, 5) -- there could be hundreds or thousands of numbers in the IN statement

EPIC FAIL!!! Can't be done without raw sql and opening up to sql injection attacks.

However, you can create a stored procedure that takes and user-defined table called ArrayOfInt. Then add EntityFrameworkExtras.EF6, which is available as a NuGet package, to call the storedprocedure and pass it an array.

WHERE with many AND/ORs with Entity Framework

Imagine you have these three tables. Product, ProductGroup, ProductGroupMembership. You want to make Products a member of a group by inserting into the ProductGroupMembership table.

You have a list of software Products provided to you. However, the input only includes Product.Name and Product.Version. You now need to check if the products exist and get the id.

SELECT Id FROM Product
WHERE (Name = 'Product 1' AND Version = '10.0')
WHERE (Name = 'Product 2' AND Version = '10.0')
WHERE (Name = 'Product 3' AND Version = '10.1')
WHERE (Name = 'Product 4' AND Version = '10.0')
WHERE (Name = 'Product 5' AND Version = '1.0')

EPIC FAIL!!! Can't be done without raw sql

However, you can add LinqKit’s PredicateBuilder to do this. PredicateBuilder works on top of Entity Framework and is available as a NuGet package. See how I used it here: Entity Framework and many WHERE clauses