Joining on multiple columns in Linq to SQL is a little different. var query = from t1 in myTABLE1List // List<TABLE_1> join t2 in myTABLE1List on new { t1.ColumnA, t1.ColumnB } equals new { t2.ColumnA, t2.ColumnB } … You have to take advantage of anonymous types and compose a type for the multiple columns you … Read more
You need to introduce your join condition before calling DefaultIfEmpty(). I would just use extension method syntax: from p in context.Periods join f in context.Facts on p.id equals f.periodid into fg from fgi in fg.Where(f => f.otherid == 17).DefaultIfEmpty() where p.companyid == 100 select f.value Or you could use a subquery: from p in context.Periods … Read more
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
This may be cleaner (you dont need all the into statements): var query = from order in dc.Orders from vendor in dc.Vendors .Where(v => v.Id == order.VendorId) .DefaultIfEmpty() from status in dc.Status .Where(s => s.Id == order.StatusId) .DefaultIfEmpty() select new { Order = order, Vendor = vendor, Status = status } //Vendor and Status properties … Read more
from p in context.ParentTable join c in context.ChildTable on p.ParentId equals c.ChildParentId into j1 from j2 in j1.DefaultIfEmpty() group j2 by p.ParentId into grouped select new { ParentId = grouped.Key, Count = grouped.Count(t=>t.ChildId != null) }
After you commit your object into the db the object receives a value in its ID field. So: myObject.Field1 = “value”; // Db is the datacontext db.MyObjects.InsertOnSubmit(myObject); db.SubmitChanges(); // You can retrieve the id from the object int id = myObject.ID;
LINQ to entities: Video walkthroughs Channel 9 video Entity framework FAQ Entity framework performance I’ve got a lot more I tagged on Delicious.com.
Some advantages of LINQ over sprocs: Type safety: I think we all understand this. Abstraction: This is especially true with LINQ-to-Entities. This abstraction also allows the framework to add additional improvements that you can easily take advantage of. PLINQ is an example of adding multi-threading support to LINQ. Code changes are minimal to add this … Read more
Since DefaultIfEmpty isn’t implemented in LINQ to SQL, I did a search on the error it returned and found a fascinating article that deals with null sets in aggregate functions. To summarize what I found, you can get around this limitation by casting to a nullable within your select. My VB is a little rusty, … Read more
Use the Take method: var foo = (from t in MyTable select t.Foo).Take(10); In VB LINQ has a take expression: Dim foo = From t in MyTable _ Take 10 _ Select t.Foo From the documentation: Take<TSource> enumerates source and yields elements until count elements have been yielded or source contains no more elements. If … Read more