adapted from MSDN, how to left join using EF 4 var query = from u in usergroups join p in UsergroupPrices on u.UsergroupID equals p.UsergroupID into gj from x in gj.DefaultIfEmpty() select new { UsergroupID = u.UsergroupID, UsergroupName = u.UsergroupName, Price = (x == null ? String.Empty : x.Price) };
No, there is no “better” way to store a sequence of items in a single column. Relational databases are designed specifically to store one value per row/column combination. In order to store more than one value, you must serialize your list into a single value for storage, then deserialize it upon retrieval. There is no … Read more
The types and the names of the properties in the anonymous types must match: new { p1 = q.QOT_SEC_ID, p2 = dpr.DPR_TS } equals new { p1 = (decimal)p.PAY_SEC_ID, p2 = p.PAY_DATE } or if p.PAY_SEC_ID were an int?: new { p1 = (int?)q.QOT_SEC_ID, p2 = dpr.DPR_TS } equals new { p1 = p.PAY_SEC_ID, p2 … Read more
A while back I wrote a 4 part blog series (Parts 1, 2, 3 and 4) covering doing bulk updates (with one command) in the Entity Framework. While the focus of that series was update, you could definitely use the principles involved to do delete. So you should be able to write something like this: … Read more
If you don’t want to execute SQL directly, the best way is to use Any(). This is because Any() will return as soon as it finds a match. Another option is Count(), but this might need to check every row before returning. Here’s an example of how to use it: if (context.MyEntity.Any(o => o.Id == … Read more
You can use the TruncateTime method of the EntityFunctions to achieve a correct translations of the Date property into SQL: using System.Data.Objects; // you need this namespace for EntityFunctions // … DateTime ruleData = Convert.ToDateTime(rule.data).Date; return jobdescriptions .Where(j => EntityFunctions.TruncateTime(j.JobDeadline) == ruleData); Update: EntityFunctionsis deprecated in EF6, Use DbFunctions.TruncateTime
First off, if you’re starting a new project, go with Entity Framework (“EF”) – it now generates much better SQL (more like Linq to SQL does) and is easier to maintain and more powerful than Linq to SQL (“L2S”). As of the release of .NET 4.0, I consider Linq to SQL to be an obsolete … Read more
Entity Framework is trying to execute your projection on the SQL side, where there is no equivalent to string.Format. Use AsEnumerable() to force evaluation of that part with Linq to Objects. Based on the previous answer I have given you I would restructure your query like this: int statusReceived = (int)InvoiceStatuses.Received; var areaIds = user.Areas.Select(x=> … Read more
Is objects a Linq-To-SQL database context? In which case, you can only use simple expressions to the right of the => operator. The reason is, these expressions are not executed, but are converted to SQL to be executed against the database. Try this Arr[] myArray = objects.Select(o => new Obj() { Var1 = o.someVar, Var2 … Read more
You cannot use properties that are not mapped to a database column in a Where expression. You must build the expression based on mapped properties, like: var date = DateTime.Now.AddYears(-from); result = result.Where(p => date >= p.DOB); // you don’t need `AsQueryable()` here because result is an `IQueryable` anyway As a replacement for your not … Read more