linq-to-sql

Mapping a database value to a TimeSpan using dbml

I need to store a time offset in a database column (for example, 12:25 AM, just the time, no date). I would like to use the nice data visual modeling capabilities in Visual Studio 2008 to generate dbml and the code to go with it. The database is Sql Server 2005. Since a TimeSpan is essentially an Int64 (aka long) containing the number ...

Linq to SQL - how to dynamically filter by columns not in select

The Problem Using Link to SQL I would like to filter by columns that I do not want returned in the select statement. This works fine as long as the query is built all at once. In situations where I try and build the where clause dynamically, I get a compile time error because the column is not included in the select. Example WORKS...

LINQ nested query

How can i write following SQL query using LINQ SELECT priority FROM Active_SLA WHERE APP_ID = ( SELECT APP_ID FROM FORM_PAGES WHERE PAGE_ADDRESS = @address ) AND PERSON_ID = ( SELECT PERSON_ID FROM PERSON_DEVICES WHERE DEVICE_NUMBER = @devicenum ) ...

Linq to SQL - Don't fetch a particular column

Is there a way to not fetch a specific column using linqtosql without having to use an anonymous type and specify each returned filed individually? We use SQLMetal to generate the dbml file which contains all the types into which to put the queried data results. However, when select columns are included in the linq query, the results g...

Is there a LINQPad equivalent to a DataContext class?

I've just begun using LINQPad and so far I like it but most tutorials I have come across for LINQ TO SQL make use of a DataContext class which is generated by Visual Studio for persisting updates etc. I am also fairly new to LINQ TO SQL so my question is what is the equivalent of the following in LINQPad (if there is one)... MyDbDataCon...

C# LINQ Dynamic Join

Duplicate: How to create a dynamic Linq Join extension method I am using System.Linq.Dynamic to create dynamic two queries var foos = db.Foos.Where(whereClause1); var bars = db.Bars.Where(whereClause2); I'd like to do a Join on the two expressions (knowing it will give an And). I have a join in code: var target = from f in foos...

LINQ to SQL and Column Alias with spaces

Still a bit new to Linq. This is driving me nuts. I want to alias a column, the alias should have a space. This works fine: Dim q = From tmp in t.Table Select iDate = tmp.iDate But, I want this to work Dim q = From tmp in t.Table Select "Some Alias With Space" = tmp.iDate Any ideas? ...

Is there an easy way to append lambdas and reuse the lambda name in order to create my Linq where condition?

I have a user control which takes a Func which it then gives to the Linq "Where" extension method of a IQueryable. The idea is that from the calling code, I can pass in the desired search function. I'd like to build this search function dynamically as such: Func<Order, bool> func == a => true; if (txtName.Text.Length > 0) { //add it...

LINQ to SQL - Tracking New / Dirty Objects

Is there a way to determine if a LINQ object has not yet been inserted in the database (new) or has been changed since the last update (dirty)? I plan on binding my UI to LINQ objects (using WPF) and need it to behave differently depending whether or not the object is already in the database. MyDataContext context = new MyDataContext()...

Linq dynamic select casting issue

I want to run a query to get a string array of distinct "logName" items for a logType. The following works great: Dim stringArray() As String = (From item In dc.Vw_Logs Where item.LogType = [Passed in logType] Select item.LogName Distinct).ToArray() However, this only works when a specific LogType is set. I would like the ...

How to Insert Multiple Rows in a foreach command with LINQ?

I wrote these lines: foreach (var catId in CatIds) { AdCategory.AdId = LastAd.AdID; AdCategory.CategoryId = catId; EngineDB.Ad_Categories.InsertOnSubmit(AdCategory); EngineDB.SubmitChanges(); } and CatIds is an Integer Array. this command inserts first element correctly but next loop causes this exception: "Cannot add...

Help Linq to Sql

Why am I getting a exception when ApplyPropertyChanges??? The code is almost the same when I'm editing a user table but is not working with my news table. The create, delete and details are all working fine but when I try to edit a news I'm getting the exception below: The ObjectStateManager does not contain a ObjectStateEntry 'MagixC...

InsertOnSubmit = Cannot add an entity with a key that is already in use

I am trying to insert a record into a table using Linq but get the dreaded Cannot add an entity with a key that is already in use error 'If the same data exists for the same patient in a record less that 21 days old then drop it Dim RecordLookup As Integer = 0 R...

LINQ2SQL performance with transactions

I'm having a major performance issue with LINQ2SQL and transactions. My code does the following using IDE generated LINQ2SQL code: Run a stored proc checking for an existing record Create the record if it doesn't exist Run a stored proc that wraps its own code in a transaction When I run the code with no transaction scope, I get 20 ite...

Refreshing a DBML diagram?

Really stupid question, sorry, but I can't find it on google (I'm sure it's in a screencast or something somewhere). I have a DBML (linq2sql classes) diagram and I've changed the underlying database. In VS2008, how do I "refresh" the diagram? There's no View-->Refresh or RightClick->Refresh or Update option. As it stands now, I have to ...

How should i lazy-generate a thumbnail image?

For background, i have a Data layer and Service layer loosely based on Rob Conery's Storefront model and like Rob's, many of my domain objects and chained with LazyList<>'s and LazyItem<>'s to make use of the deferred execution that Linq2Sql provides given that my Lazy* types utilise IQueryable<T> rather than this awesome delegate approa...

Linq to SQL Select in Stored Proc

I have a stored proc called via LINQ to SQL the stored proc calls another stored proc within it and has a SELECT to output the result. the SELECT result doesnt' get returned to my LINQ to SQL, I can only get the int result value. How can I get the select result of a stored proc with is within a stored proc ...

LINQ to SQL Take w/o Skip Causes Multiple SQL Statements

I have a LINQ to SQL query: from at in Context.Transaction select new { at.Amount, at.PostingDate, Details = from tb in at.TransactionDetail select new { Amount = tb.Amount, Description = tb.Desc } } This results in one SQL statement being executed. All is good. However, if I attempt to ret...

Detect if linq entity needs inserting

Usually I have an integer identity primary key I can detect if an object needs to be inserted by doing public partial class MyObject { public void Save(DataContext context) { if (this.PrimaryKey == 0) context.MyObjects.InsertOnSubmit(this); context.SubmitChanges(); } } But in the case I'm working on the pri...

LINQ to SQL using GROUP BY and COUNT with Date (CONVERT problem)

Hi. I have read this question but it's not quite what I was looking for. My problem is I whant to do this: SELECT CONVERT(varchar(10), m.CreateDate, 103) as [Date] , count(*) as [Count] FROM MEMBERS as m WHERE m.CreateDate >= '24/01/2008' and m.CreateDate <= '26/06/2009' Group by CONVERT(varchar(10), m.CreateDate, 103) result is: ...