linq-to-sql

LINQ to SQL: using string filename to create a DataContext at runtime

Hi, With FileInfo I can find the file name of a DataContext .dbml file. Provided I declare: Dim DataModel = New AttributeMappingSource().GetModel(GetType(NorthwindDataContext)) With System.Data.LINQ.Mapping I can find the name of all Tables and furthermore their Columns and relationships. All this thanks to the excellent post from ...

How do I map common columns to every class?

I have a set of fields (timestamps, user info, etc) that are common to just about every table in my database. Is there a way to associate these columns without having to tediously add them to every single entity class? My first thought was to have a common base class holding these fields with the column mappings but without the Inher...

Using NHibernate/LINQ to detect and write changed rows from XML files into a database?

Hello all, I have a situation where an XML file contains data similar to some content in database tables, and I would need to update the database to match exactly the contents of that XML file. I am wondering, if using NHibernate, or LINQ to SQL, I am able to do something like this: Load the database content into entity objects. Load ...

Linq to Sql - Set connection string dynamically based on environment variable

I need to set my connection string for Linq to Sql based on an environment variable. I have a function which will return the connection string from the web.config based on the environment variable, but how do I get Linq to always use this "dynamically created" connection string (preferably without having to specify it every time)? I kn...

LINQ to SQL: Child property is null even though it was returned

Scenario: A Customer entity has properties for State and Color. i.e. Washington and green. The SQL Server foreign key relationships are setup fine, and all have correct data with proper referential integrity. Consider this LINQ to SQL query: var cust = (from c in db.Customers join s in db.States on c.StateID equals s.ID join cl in ...

Need help with a linq query please!

I am trying to write a query that will search for "orders" that contain a certain "product" and I'm having a little difficulty getting it to work. Basically, this is what I'm trying to do: Dim orders = From o in db.Orders _ Where o.OrderProducts.Contains(Function(p) p.ProductID = 123) _ Select o I've also tr...

LINQ to SQL: Is it possible to reference the data context when extending a table object?

If I'm extending OnCreated for a LINQ to SQL table object, is it possible to get a reference to the data context to which the table belongs? For example, if I add a property to the data context: Partial Class MyDataContext Private _myValue As String Public ReadOnly Property MyValue As String Get Return _myValu...

What does Linq replace a new line with when updating?

I have always used Replace(myString, vbCrLf, "<br/>") to show line breaks when outting something to a page from the database (retaining line breaks). I am now using a DetailsView that has a textarea as one of the fields and uses a LinqDataSource as its datasource. I want to allow users to type line breaks in the textarea and display th...

LINQ deletion - Can delete one way, can't using Single enumerable

This works: var i = (from x in db.Test where x.Id == 1 select x).First(); db.Test.DeleteOnSubmit(i); db.SubmitChanges(); I get a cast error for this (int/string): var i = db.Test.Single(x => x.Id == 1); db.Test.DeleteOnSubmit(i); db.SubmitChanges(); I was also able to make an update using Single sucesssfully on the same ta...

Linq To Sql: Exception "String must be exactly one character long"

Consider a SQL Server table defined with a varchar(1) NULL field. It's being used to store a gender character. Some rows have data, some not: either null or blank. Granted the blanks SHOULD be nulls, but consider that blank is a valid value here. I'd much prefer the value to be null. ID Gender 1 'M' 4 'M' 3 '' 4 ...

Convincing a die hard DBA to use an ORM for the majority of CRUD vs Stored Procedures, View, and Functions

I have been working with NHibernate, LINQ to SQL, and Entity Framework for quite some time. And while I see the benefits to using an ORM to keep the development effort moving quickly, the code simple, and the object relational impedance mismatch to a minimum, I still find it very difficult to convince a die hard SQL dba of an ORM's stre...

Using Linq to SQL, how do I Eager Load all child and any nested children results

I have 5 tables in a L2S Classes dbml : Global >> Categories >> ItemType >> Item >> ItemData. For the below example I have only gone as far as itemtype. //cdc is my datacontext DataLoadOptions options = new DataLoadOptions(); options.LoadWith<Global>(p => p.Category); options.AssociateWith<Global>(p => p.Category.OrderBy(o => o.So...

How to create a strongly typed MVC View based on a custom Linq2Sql class

I have created a custom entity because I need to populate an entity with some data from a join in L2S. When I right click on the ActionResult code in the Controller to "Add View", and then choose "Create strongly typed view", my class doesn't show up in the classes available in the selector. I'm not sure why. Here is my code: //The Mod...

Is there any way to rewrite this LINQ query so that it only does one SQL query?

Hi, I have a LINQ query which is fetching a list of nested objects. from c in ClientsRepository.LoadAll() orderby c.Name select new ComboBoxOptionGroup { Text = c.Name, Options = from p in c.Projects orderby p.Name select new ComboBoxOption { Text = p.Name, ...

Search in LINQ to SQL

hi I want to Search in my Table. I write follow Code : var w = from act in Movie_List.Actors where act.Actor_Name == Snametxt.Text select new {act.Actor_Name}; Acttxt.Text= w.SingleOrDefault().Actor_Name; this code work only for FIRST search and when I want to search again , appear exception ...

Linq query works with null but not int? in where clause.

I have a linq query function like (simplified): public IList<Document> ListDocuments(int? parentID) { return ( from doc in dbContext.Documents where doc.ParentID == parentID select new Document { ID = doc.ID, ParentID = doc.ParentID, Name = doc.SomeOtherVar ...

How does MSLinqToSqlGenerator determine the return type of a stored procedure?

Hello gurus, I have a stored procudre (SP) as follows: CREATE PROCEDURE [dbo].[UDSPSelectMissing] -- paramters omitted AS BEGIN -- Code Omitted SELECT c.MemberID, c.FirstName, c.MiddleName, c.LastName, c.Suffix, c.PhoneHome, c.PhoneCell, c.Email FROM [dbo].[Members] c WHE...

How to refresh a GridView/LINQ data source from textbox onchange event

I've been having troubles getting my textbox to refresh a GridView from the onchange event. The GridView is linked up to a LINQ data source and the LINQ data source has a Where Parameter UserId that it gets from the textbox... Here's the code: <asp:Label ID="label_UserId" runat="server" Text="Search by User Id: "></asp:Label> <...

Binding error in linq to sql

hi how do I solve this problem? Exception : This causes two bindings in the collection to bind to the same property. Parameter name: binding Code : var q = Movie_List.Actors.Where(a => a.Actor_Name == Snametxt.Text); Acttxt.DataBindings.Add("Text", q, "Actor_Name"); ...

How to extract extra data from LinqDataSource?

I have a simple wizard generated query in a LinqDataSource like this: <asp:LinqDataSource ID="EvaluationsData" runat="server" ContextTypeName="Bonus.Models.BonusDataContext" Select="new (Id, Name, Coverage)" TableName="BM_Evaluations" </asp:LinqDataSource> I assign this data source to a DropDownList, using Id and Name as the Data...