linq-to-sql

LINQ to SQL querying against a view to populate an object model

We have the following data model: CalendarAppointment 1 <------> * AppointmentRole * <--------> 1 Person (or Group) A calendar appointment can have multiple persons associated with it. Each person can be in a different role (attending, driving, picking up or dropping off). (A person can also be a member of a group and groups can be ...

Inserting Order automatically insert the User Entity also

I tried to insert Order Row with the Existing User using LINQ. The program should only insert the Order & Order Details because the User is already exist and I don't want to add the new user for each order. Instead of that, the program is now inserting Order, OrderDetails together with User. So, I am getting duplicate users in my us...

LINQ to SQL query against joined tables with criteria from both tables resulting in hierarchical object model

We have a calendaring application in which each appointment can have multiple persons associated with it. Appointment 1 <--> * AppointmentPerson Appointment Date ID AppointmentPerson AppointmentID (FK to Appointment.ID) PersonID The simplest query is to find all appointments for a given person and date. Using LINQ to SQL we...

Why would reusing a DataContext have a negative performance impact?

After a fair amount of research and some errors, I modified my code so that it creates a new DataContext each time the database is queried or data is inserted. And the database is queried frequently - for each of 250k transactions that are processed, the database is queried to obtain a customer id, department id, and category before the...

Casting objects created in LINQ to SQL to a single master object.

I have an interesting problem to solve that would be helped by successfully casting objects created by LINQ to SQL into a single master object that I could pass around. Here is the scenario at a high level. I have a number of stored procedures that fetch data and then all return the exact same columns. The params into the procs and th...

Adding Updating database records with LINQ

I need a LINQ procedure to fulfill the following need: I need to add a record if the record does not exists in the data base table or It should update the records if the record does exists in the table in this case procedure only update the record if the record has any changes only.I have done it but feel it can be enhanced by reducing ...

LINQ 2 SQL with WCF and serializing EntityRef

Hi all, I have a LINQ 2 SQL project and I'm trying to use it in WCF. EntitySets serialize perfectly if I specify them with the LoadWith dataloadoptions. The problem is EntityRefs. I have a customer with 1 address. But 1 address can have multiple customers. How do I make sure that the Customer is passed over the line WITH the Addres...

Can I reuse the connection from DataContext in Linq to Sql?

DataContext has Connection property of type DbConnection and I was wondering if I could re-use it. I've tried to use it creating a command using CreateCommand and using a reader off of it but I've gotten errors saying there is a pending transaction or something similar. Basically I'm trying to find out if there is a best practice or gu...

Trigger cache clearing on table crud operation in linq to sql

I have a method that gets all the records from a particular database, then stores it in the cache. The next time that method is called, it first checks the cache to see if it can simply return a cache version, if that cache object hasn't been expired. Question: how do I trigger a method everytime dataContext.SubmitChanges() is called? ...

Very weird behavior with Linq to SQL and MVC

I have an Action that requires authentication. The action method creates some records with foreign keys using Linq to SQL. When the user calls the action and is logged in the method works without a problem. When the user is not logged in, MVC redirects them to the login page with the returnUrl parameter. After a successful login the acti...

Sort GridView Bound to DataTable

I have a repository that contains all of my LINQ queries for this project that I am working on. I am able to get the LINQ results to a DataTable and bind that to a gridview for displaying the data. Now I need to make the gridview sortable. I have set AllowSorting="true" and I have the OnSort event handled in a routine in the codebehind. ...

Custom date format from Linq to SQL query

Im using the folowing function to return a SelectList. I need a custom format on Descr, but replacing Key.ToString() to Key.ToString("DD/MM/YY") render me the error "Method 'System.String ToString(System.String)' has no supported translation to SQL.". How could i use a custom date format on Descr? Public Function ReturnDates(ByVal P...

How do I return a table in LINQ that relies on a join/subquery?

I need the fields in 1 table contingent on 1 property matching rows in another table. I can write this query in SQL with a subquery as such: SELECT * FROM Table1 WHERE Property1 IN ( SELECT Property1 FROM Table2 WHERE Property0 = 1 ) But I read here that it's less complicated and just as easy to write with a join, which I ...

Linking Multiple Tables in LINQ to SQL

I would like to get the list of albums (Distinct) which was sung by the artistId=1 I am very new to LINQ to SQL and do not know how to join multiple tables. Please see the database diagram below: SingBy is the middle table between Track and Artist. How could I achieve this? ...

How do you use a linq to sql join two tables and return the result?

I have been looking on Stack overflow but none of the answers seem to fully sort out this problem. The error I'm getting is: Cannot initialize type 'mvcTest.Models.MakeModelSpec' with a collection initializer because it does not implement 'System.Collections.IEnumerable' I have created a new type like this to get over the anonymous typ...

Is it possible to insert large amount of data using linq-to-sql?

I need to insert large amount of data into SqlServer 2008. My project is based on linq-to-sql. I process csv file with 100.000 rows. Each row is mapped to Order object. Order contains also collection of Category and Code objects. I need to map each row to object in order to validate it. Then I need to insert all these objects into dat...

How can i add list<myclass> data into sqldatabase?

i try to add data with ExecuteCommand. everything is ok if list<string> data. i converted string type list into arraylist to add sql. But <MyClass> there are rows and column. look below picture. public class MyTally : ISave { public List<ENG_MPD> engMpdIntersectList { get; set; } private ArrayList engMyTallyList; ...

How can i add data programatically in linqqtosql?

i dislike below methods. Because more fields (suchas 150 fields) it is not good method. There is any SetValue method to add datato sql linqtosql for submitchanges? MyClass c = new MyClass (); c.FirstField = "bvnvb"; c....... c... c... // Too many rows there is... ...

How to force LINQ to SQL to pass .NET DateTime as datetime2?

I have a simple query in LINQ to SQL: var id = 23479824; var date = Changes.Where(ch => ch.Id == id).First().Date; var diff = Changes.Where(ch => ch.Id == id).Select(ch => SqlMethods.DateDiffNanosecond(date, ch.Date)).First(); The diff variable should be zero, however is not. The generated SQL is following: DECLARE @p0 DateTime = '20...

Possible to update a member for all objects in a collection using LINQ?

Say I have the code below that uses LINQ to SQL: MyDataContext dc = new MyDataContext; var items = from f in dc.TableName where f.ChildId == 4 select f; If the table TableName has a column called Completed is it possible for me to set the Completed column to true for everything selected above? ...