linq-to-sql

Is the DataContext class specific to SQL Server?

The MSDN page for the DataContext class says: Represents the main entry point for the LINQ to SQL framework. Yet it looks like the constructors will take any ADO.NET IDBConnection. Am I right in thinking that a DataContext can wrap any ADO.NET connection? Or are there special things that need to be considered when using a connecti...

Is possible to use generics in LINQ-to-SQL mapping?

Hi all, Is there a way to define the following structure in a DataContext/DBML file? public class Entity { public int Id { get; set; } public string Name { get; set; } public EntitySet<IPermission> Permissions { get; set; } } public class User : IPermissionHolder { public int Id { get; set; } public string Name { ge...

Dynamic Where in Linq to SQL

How would I get something like this to work so that I can dynamically alter the where-clause in this linq to sql query? Dim AccountID = 1234 Dim AccountList Select Case Types Case 1 AccountList = (from a in dc.Accounts where a.ID = AccountID) Case 2 AccountList = (from a in dc.Accounts where a.ID = AccountID An...

Merge content of two tables with LINQ-to-SQL

I have two tables in my database Table:Documents Id (int), DocName (nvarchar) ---------------------------- Table:AccessLogs Id (int), DocId (int), AccessTime (DateTime) ---------------------------- How can I write a LINQ query that returns the last 10 accessed documents and fills in the access time from the accesslogs table? I have m...

Returning dates without times in a LINQ query

I am writing a query where I want to count the number of times our call center gets contacted by date. Seems easy enough, but since the contact date field is a datetime field I get the time, so when I group by contact date(time) each contact date instance has a count of '1'. So, I want to group by just the date without the time. Below...

Can I use a IComparer in .OrderBy() in Linq-to-SQL?

This question (LINQ and a natural sort order…) talks about how to implement natural sorting in Linq using an IComparer. I've used this successfully in the past with IEnumerables, but I am unable to make it work in Linq-to-SQL expressions. Is this because the specific overload of .OrderBy() that takes an IComparer is not supported by Li...

<column> IS NULL vs <column> = NULL in LINQ to SQL generated SQL

in ASP.NET MVC 2.0 I'm doing something like the below (specifically, matching a record based on some values as well as some NULL values). var result = dataContext.Inventory .SingleOrDefault(x => (x.part_id == model.Part.id && x.location_id == transaction.to_location_id && x.bin_id == tran...

The null value cannot be assigned to a member with type System.Decimal which is a non-nullable value type

I have a Job Register Table and that table doesn't have any records. This is my LINQ code: Dim QRecordCount = (From LC In CntxtJobDetails.JobRegistrations _ Where LC.JobCode <> 0 _ Select LC.JobCode).Max() + 1 When I execute the code above it gives me the following error: ...

Linq-to-sql: Datacontext - missing table issue

I have issue with table "Reality" which is not found, when I type "db" and press dot it is not suggested to me and even when I type it manually it is not found. DataClasses1DataContext db = new DataClasses1DataContext(); var query = db.Reality I also could not see it in Object browser Even when object "Reality" is alone...

Selecting Many Fields From a Table using Linq and Lamda Expressions

I have a DataContext (db) that can access the tables in my SQL Express database, from which I would like to extract only three of the multiple fields in the tblItem table: // this does not work - what is the correct way to do it? var items = db.tblItems.Select(i => i.id && i.name && i.totalAmount); The intention is to spit these out...

How do I return an IQueryable from Linq to SQL query when the dbContext is with a 'using' block?

I've been coding with 'using' blocks but I am wondering if I can return an IQueryable from the following without the object being disposed before I access it. public IQueryable<Contact> GetContacts(string clientID) { using (dbDataContext db = new dbDataContext()) { var contacts = from _contacts in db.Contacts ...

How to create LINQ to SQL Group by with condition?

How can I create LINQ to SQL request where I can use group by with condition? For example: from ri in resItems group ri by new {groupByPackaging ? (ri.Model, ri.Condition, ri.Packaging) : (ri.Model, ri.Condition)} into g select new { ... } ...

What is the correct way to dynamically add an undetermined number of clauses to a Linq 2 Sql query?

This function is used to return a contact list for a users search input. The number of search terms is always at least one, but could be many. public IList<Contact> GetContacts(string[] searchTerms) { using (dbDataContext db = new dbDataContext()) { var contacts = from _contacts in db.Contacts orde...

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

I'm having a bit of trouble trying to add an object to the database using LINQ to SQL. I have three tables in my database: -------------------------------------------- tblShows -------------------------------------------- ShowID | Name -------------------------------------------- -------------------------------------------- tblShow...

ORM Comparison: Which comes first the database or classes?

Lately I've been looking into the .NET based ORMs that are available. I've noticed that each ends up siting in one or two camps. In one camp the database is created first, and the ORM provides an easier way to access the database in an application. In the second camp the object model exists first and the ORM facilitates persisting the ob...

ForeignKeyReferenceAlreadyHasValueException when creating Lookups using LINQ to SQL

Hi to everyone... I am new to C# and .NET and I just started to study LINQ to SQL, I like it. But.. I found this one very annoying thing about it. Implementing lookUps is very complex because of the "ForeignKeyReferenceAlreadyHasValueException"! There is just NO simple straight-forward way of doing it! I noticed, if I delete all associa...

Visual Studio: How do you refresh metadata for Linq to Sql/Entity Framework

I have a view that I have been trying to map in either entity framework or linq to sql. However when querying the view it just crashes horribly (it is just this view). Because the error message is completly generic I thought I would just divide and conquer, and delete half the columns on my view (in sql server), and then update the Dat...

Why is this Linq Query returning 3 identical rows from the same SQL view?

I have a simple view defined MSSQL 2008. The view is defined as follows: SELECT dbo.tblCompany.CompanyID, dbo.tblAccount.Carrier, SUM(ISNULL(dbo.tblLine.LineCharge, 0)) + SUM(ISNULL(dbo.tblLine.FeatureCharge, 0)) + SUM(ISNULL(dbo.tblLine.AccessCharge, 0)) AS SumOfCharges FROM dbo.tblCompany LEFT O...

To set a bool value if any of the column in empty in Sql Table using Linq in C#?

Hi, I need to get the details from the table using LINQ in C# and the where condition is ID and here my condition is ,if any of the column in the selected row is empty or null i need to return as False if not means True should be returned.....Cn any one help for this...Thanks in advance. ...

Why can't I update data into database using LINQ to SQL?

I am trying to update data while I am reading them from database, see below. But after the whole thing finish, the data didn't get updated. Is there any transaction syntax i need to specify? (When I debug, I can see I have the right record retrieved.) using (conn = new SqlConnection(MyConnectionString)) using (SqlComman...