linq-to-sql

Save/Automate LINQ to SQL Adjustments in Editor

I'm generating domain model using LINQ to SQL via the VS2008 built-in editor. That works really well, too; when I adjust my database schema I simply delete everything from the editor and then pull it back in from the server explorer by selecting all tables and dragging them into the designer surface. That works great too. Now the proble...

LINQ-to-SQL Compiled Query Problem (works as uncompiled query)

Hello, I have C# extension methods on IQueryable, e.g. FindNewCustomers() and FindCustomersRegisteredAfter(int year) and so forth which I use to "chain" a query together for LINQ to SQL. Now to my problem: I want to create compiled queries, e.g.: private static Func<MyDataContext, SearchInfo, IQueryable<Customer>> CQFindAll ...

Preventing recursion during DataContext.SubmitChanges()

This is a bit of a poser, and I hope you'll find this challenging problem as interesting as I do... :) I have a subclass of DataContext called MyDataContext, wherein I have overridden the SubmitChanges() method with some code of the form: BeginTransaction(); // my own implementation IList<object> Updates = GetChangeSet().Updates; forea...

In the ORM designer, why don't the server objects get refreshed properly with new changes?

Whenever I use the ORM deisgner in LinqToSql I have real problems trying to get the latest objects from the server explorer after i've updated them in the database. For example, I have dragged in a stored procedure to handle my insert/updates, which creates the method for me to call. Problem is I then have to go and change one of the p...

Set database name dynamically in LINQ to SQL

I am using LINQ to SQL to connect to database from my application. When I am changing environment from production to staging, I can update my connection string in web.config. But there is one more value I need to update when environment changes. That is database name. In LINQ to SQL designer file, database name is mentioned as attribute ...

Join and Calculate Average of column using LINQ to sql

I have two tables Video VideoId, VideoName, VideoUrl Comment CommId, VideoId, CommentDesc, Rating I want to join and get average rating for video using LINQ How can I do this? ...

how to add an empty row in a IQueryable?

If you had a filled IQueryable variable. How would you add a new empty entry in the end of that list? Here is what I have to do: if there is 2 entry, add 3 empty entry if there is 0 entry, add 5 empty entry if there is 5 entry, add 5 empty entry You can now see the pattern and it's for a repeater in asp.net. Should I simply create...

Generate FULL JOIN with LinqToSQL

I have this linq query : (from rapportBase in New_RapportReferencementBases join rapportExtensionAll in New_RapportReferencementExtensionBases on rapportBase.New_RapportReferencementId equals rapportExtensionAll.New_RapportReferencementId into jointureRapportExtension from rapportExtension in jointureRapportExtension.DefaultIfEmpty() ...

How do you output a column in Upper Case in a LINQ to SQL Query?

I would like to UCASE or ToUpper a column in my LINQ query. var query = from rsn in db.RSLReasons orderby rsn.REFCMNT select new {rsn.REFCODE, rsn.REFCMNT}; dtReasons = query.ToADOTable(rec => new object[] { query }); If I try to run the following code: var query = from rsn in db.RSLReasons order...

Using Stored Procedures with Linq To Sql which have Additional Parameters

I have a very big problem and can't seem to find anybody else on the internet that has my problem. I sure hope StackOverflow can help me... I am writing an ASP.NET MVC application and I'm using the Repository concept with Linq To Sql as my data store. Everything is working great in regards to selecting rows from views. And trapping very...

Managing child lists with LINQ to SQL

I have a simple parent-child situation where the parent can have multiple children. The user can update the list of children at will by selecting or deselecting them from a listbox. I try updating the child list using code like below but I get a SqlException: Violation of PRIMARY KEY constraint 'PK_Child_1'. Cannot insert duplicate...

EntitySet<T>.Where(myPredicate) throws NotSupportedException

EDIT: Let's try this again. This time I've used the AdventureWorks sample database so you can all play along. This will rule out anything crazy I've done in my own database. Here's a new example demonstrating what works and what I would expect to work (but doesn't). Can anyone explain why it doesn't work or suggest a different way of...

resultSet.Expression {System.Data.Linq.SqlClient.SqlProvider+OneTimeEnumerable (...)}

I'm having a compiled query after which I've stacked a non-compiled query via an IQueryable extension method. var resultSet = CompiledQueries.GetNewCustomers(datacontext).OrderBy(criteria.OrderExpression); When I put a breakpoint at a non-compiled query, I can usually see the SQL expression generated. However, when I put a breakpo...

Get cached Entity by ID without call to db.

How can I implement GetItemFromCacheById function, such that it takes object from linq-to-sql cache, instead of calling the database. using (var cxt = new DbDataContext()) { // Read item into the cache Item item = cxt.Items.Where(x => x.Id == 1).Single(); //... item = GetItemFromCacheById(cxt, 1); } ...

Is there a better tool to manage LINQ2SQL DataContext files?

I have a rather large LINQ2SQL for a project that I am working on. I must confess that I primarily use the WYSIWYG editor provided by Visual Studio to manage and build the structure. I am starting to run into a problem when it comes to locating entities on the diagram and just working with it in general. Is there a better WYSIWYG to...

Workaround for LINQ to SQL Entity Identity Caching and Compiled Query Bug?

I've come across what appears to be a bug in linq to sql where identity caching does not work when performing primary key queries inside of a compiled query. I wrote the following sample to demonstrate the usage of identity caching. It only executes one call to the database the first time it's hit, every time after that it retrieves the...

ASP.NET/MVC Linq to SQL Update a Row

I have a simple row that has 4 Columns { [Primary Key Int]RowID, [text]Title, [text]Text, [datetime]Date } I would like to allow the user to edit this row on a simple page that has a form with the fields "Title" and "Text". There is a hidden field to store the RowID. When the user posts this form to my controller action, I want it to ...

How do you compare dates in a LINQ query?

I am tring to compare a date from a asp calendar control to a date in the table.... here's what i have... it doesn't like the == ? var query = from details in db.TD_TravelCalendar_Details where details.StartDate == calStartDate.SelectedDate && details.EndDate == calEndDate.SelectedDate ...

Generate Table/View schema from LINQ-TO-SQL DBML file

I'd like to have a single source of the description of the data structure. Some people are asking can the DBML file being refreshed when it is changed in the database. The way I do is stupid but common; open it, delete all, and drag-drop again. I heard that there are some 3rd party do the tricks. But I am thinking, any way to inverse t...

Not load entire relation

I have a simple database from which I am generating Linq2SQL classes using a datacontext. In one part of my application I would like to load the entire relationship such that I get child records. In another part of the application I am passing that relation across the boundary between model and view and I would like to not pass the ent...