linq-to-sql

Can the where-condition in this LINQ-query be optimized?

Hey! I have this LINQ-query: bool? a, b, c; from i in _ctx.SomeEntitySet where (a == null ? true : i.IsA == a) && (b == null ? true : i.IsB == b) && (c == null ? true : i.IsC == c) select i; I only want to take the condition IsX == x in...

I set IDENTITY_INSERT to ON but I get a SqlException saying it's OFF

I'm trying to grab thousands of invoices (and other stuff) from a text file and insert them into SQL Server 2008. I wrote a little console app to do this and it uses LINQ to SQL. After I insert all the existing invoices I want the Invoice_ID to be an identity column and auto-increment, so I have it designed as such: CREATE TABLE [dbo]...

Using a single GridView and a LinqDataSource, how would you update data from a parent table and a child table?

I'm using a GridView and a LinqDataSource to display data from an entity called CHILD in this example. CHILD has an association with the PARENT entity. I'm having no problems displaying data from both these entities in a single GridView since related PARENT records are exposed as a property in CHILD. However, I can't figure out a wa...

LINQ and SQL Server performance tuning the SQL Server 2008 database best practice?

My question is what is best practice to optimize performance using LINQ for SQL And performance is response time out in the user interface. Right now I have some sales data in a SQL Server 2008 database and I display this data (MAT, yearly, in different segments, growth in segment, percent of market growth ,,,,) in charts in a ASP.NET a...

Setting Linq to SQL Type Names in Visual Studio

I love Linq but it can rapidly clutter up the namespace with automatically generated types. Usually these automatically generated types are often irritatingly close to other objects leading to many hours of fun and laughter. In the designer I notice that I can specify the table names, however I can not for the life of me see how to set ...

Linq to SQL, Return only users in a particular role

Hi, I'm using linq to SQL along with asp.net membership provider. I have three roles set up but want to get only the users of a particular role. This is my query to return all users public IQueryable<aspnet_User> GetAllMembers() { return db.aspnet_Users.OrderByDescending(u => u.UserName); } Can anyone point me in the right ...

Is there any way I can execute this SPROC and get the result with Entity Framework?

This SPROC "returns" a table with two columns: Id, Score. Is it possible to execute this SPROC and include the custom data-type as parameters from within Entity Framework? ALTER PROCEDURE [presenta].[SearchLpi] // The presenta.IdTableType is a table with just one column "Id" @selectedLpis presenta.IdTableType READONLY AS BEGIN...

Linq Query Question

I have this code that returns a caseID from an Alleged Perpetrator table. This table also has a column "LastName". I want to search on caseID and return LastName but I don't know how to code it. I've been on the microsoft site looking for LINQ to SQL examples but still can't figure it out. Any help would be greatly appreciated! Ken pub...

How could I modify this query to get what I want - Calendar dates

Hi I have this query return plannerDb.IndividualCalendars.Where(u => u.UserId == userId && u.StartDate.Date >= startDate && u.EndDate.Date <= endDate) so basically what this query does is that is looks for all calendar events that range from the start of that month to the end of that month. However this does not accou...

My LINQ is really inefficent

I'm using LINQPad to learn LINQ and I've run into a stumbling block. The goal is to get a list of Network Ids, Network Names and how many Stations each has. Here is my original SQL: SELECT n.iStationId AS NetworkID, n.sPrettyName AS NetworkName, COUNT(s.iStationID) AS StationCount FROM T_StationInfo AS s, T_StationInfo as n WHERE s.iN...

LINQ to SQL - Restrict Relationship by Condition (One to Many) (1 - M)

OK, first off, I'm brand new to LINQ2SQL so excuse my ignorance. I've done some searches, but after 1 hour, I decided to come here. Here goes: I'm trying to do something that I think is very simple. It could just be that I don't understand. I'm using LINQ 2 SQL, via the VS designer. I have 2 tables: Clients and Categories. A client ca...

Doing a Count in a Linq to SQL Query (created as IQueryable) gets all the rows

Class Customer has the following method, which returns an IQueryable with the linq query to get the active sales for a customer: public IQueryable<SalesHeader> QueryCurrentSales() { // this.SalesHeaders is an association defined in the DBML between // the Customer and SalesHeader tables (SalesHeader.CustomerId <-...

LINQ to SQL Downside and Limitations

What are the downsides and limitations of using Linq to Sql verses writing a more traditional data layer calling stored procs/using dynamic sql through the .NET SQL Server data provider? The advantages are well documented but I’ve found little discussion of the real world issues people have experienced. Please note I’m not talking abou...

Object reference not set error INSIDE != null only if block?

Repository code: public IQueryable<Bundle> getAllCustomBundlesByCompanyID(int companyID) { return from companyBundle in db.CompanyBundles join bundle in db.Bundles on companyBundle.BundleID equals bundle.BundleID where companyBundle.CompanyID == companyID && bundle.CompanyID == companyID orderby bund...

How can I modify this linq query to return some empty fields instead of just omitting the data that doesn't fit the join?

public IQueryable<RecentlyCreatedAssetViewModel> getRecentlyCreatedAssetsByCompanyID(int companyID) { return (from a in db.Assets join ab in db.AssetBundles on a.AssetID equals ab.AssetID join b in db.Bundles on ab.BundleID equals b.BundleID where a.Company...

How to groupBy in linq to sql?

Hi I have data like this id = 1<pk> SDate = 12/12/2009 EDate = 12/12/2009 binding = a_1 userid = 14 id = 2<pk> SDate = 12/12/2009 EDate = 12/12/2009 binding = a_1 userid = 14 I want to group my data by binding. I am not sure how to do this though. Do I have to make a new select to do this? so far I have this Db.Table.Where(u => u...

LINQ to SQL business object creation best practices

I've been using LINQ extensively in my recent projects, however, I have not been able to find a way of dealing with objects that doesn't either seem sloppy or impractical. I'll also note that I primarily work with ASP.net. I hate the idea of exposing the my data context or LINQ returned types to my UI code. I prefer finer grained contr...

LinqToSql - "insert on submit"

hi I'm trying to insert a row in to one of my tables, so I look over the web to find an example for using the DATACONTEXT and found this one: protected void buttonSave_Click(object sender, EventArgs e) { using (NorthwindDataContext context = new NorthwindDataContext()) { Customer customer = new Customer { CustomerID =...

Problem with adding record using LINQ to SQL

I try to add a record to SQLServer db table using LINQ to SQL in my WPF app, but always get an error regarding missing directive. Usually intellisense gives a hint on such issue, but not this time. Here my code with all directives: using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Windows...

Is there a "for xml path" equivalent in LINQ to SQL?

I have a table of blog entries, a table of tags, and a table that intersects the tags to a blog entry. I want to roll-up the tags of a blog entry into a comma delimited string to be returned in the same result set. This is how I've done it in SQL: select     be.Title     ,Tags = lower((         select             stuff((             ...