linq-to-sql

Is this Repository pattern efficient with LINQ-to-SQL?

I'm currently reading the book Pro Asp.Net MVC Framework. In the book, the author suggests using a repository pattern similar to the following. [Table(Name = "Products")] public class Product { [Column(IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert)] public int ProductId { get;...

LINQ Query to return a list of records that do NOT have an associated record in the join.

What is the most efficient way to return a query where the result set is those records that do NOT have an associated record? I am using LINQ and Visual Basic and have been trying to construct a left outer join and then filter on those records whose id is nothing or null in the associated table but am so new to this that I can't get any...

Select All columns for all tables in join + linq join

How to select all columns from tables in join using linq Sql: select CTRL_RUN_JOB.*, CTRL_DATA_STREAM.* from CTRL_RUN_JOB inner join CTRL_DATA_STREAM on CTRL_RUN_JOB.DATA_STREAM_ID= CTRL_DATA_STREAM.DATA_STREAM_ID Linq: from CTLJCRJOB in CTRL_RUN_JOBs join CTLRFDSTM in CTRL_DATA_STREAMs on CTLJCRJOB.DATA_STREAM_ID equals ...

What's the difference between multiple where clauses and && operator in LINQ-to-SQL?

It looks like I can write a where x.a==1 && x.b==1 as where x.a==1 where x.b==1 As I understand the latter turns into .Where(x => x.a == 1).Where(x => x.b ==1), but how does this translate to DB? Which would be better in terms of optimization? I can always look at the executed query from profiler but that would hardly be a generaliza...

In LINQ to SQL, how do you determine what column elements is a sub-set of another column (i.e. Like-Sql statement)

Here is some code: //all possible search terms of interest searchTerms = from s in dc.SearchTerms select s.term; //all possible results var results = from r in dc.Data select r.hyperlinks; I want to perform an operation where I get all "r.hyperlinks" that contains s.term. It is something like r.hyperl...

How do you force linq to sql to use the correct data type for sql parameters?

I have a situation where a certain linq query that runs against a multi-million row table is taking far to long to run. I dissected the linq query output and found that it was creating the parameters for the where clause using the wrong data type. For instance, one field was defined as a Char(12) in the database, but the parameter it was...

What is different linq to sql where clause

what is the different between the two 1) context.connection.open() var albums = (from a in context.Albums where a.id == id select a); context.connection.close() 2) context.connection.open() var albums = (from a in context.Albums select a); context.connection.close() var result = albums...

NAnt build c# project with Linq to SQL Classes

I'm in the midst, after many hours of researching how to automate the build process, writing a nAnt build script for a project that works with COM interop (not by choice... just the only way to integrate with the host package). I'm trying to figure out, one of my dll's happens to contain a Linq to SQL class in it, and I'd like to, if at...

What is the best LINQ-to-SQL strategy for disposing of DataContext within methods?

I understand that it is good practice to use a using block when getting data from the LINQ-to-SQL classes as shown below. However, when I do this, I can only access the shallow properties of orders (e.g. Order.OrderId) but not the deeper properties (e.g. Customer.CustomerName) since they seem to be disposed of at this point. I can take...

Is there an advantage to USING vs. declaring a context variable?

These two snippets do the same thing - is there one that's better than the other, or is it just a matter of preference? Using context As MyDatabaseDataContext = New MyDatabaseDataContext() Dim test = context.Employees.Count End Using vs. Dim context As MyDatabaseDataContext = New MyDatabaseDataContext() Dim test = context.Employe...

LINQ syntax help: projection and grouping

New to LINQ.. I am curious as to the syntax to do the following SQL query in LINQ SELECT MAX(TMPS), DAY FROM WEATHERREADINGS GROUP BY WEATHERREADINGS.DAY What I have so far: var minTemps = from ps in ww.WEATHERREADINGS group ps by ps.DATE.Hour into psByHour select new { ...

Linq to Sql: Help with InvalidCastException in my query

I have written a SQL query that works just fine, but am having a little trouble with the conversion to LINQ. Here is the SQL: SELECT CourseID, CourseName, CreditHours, CPTRequired, COTRequired, CPTElective, COTElective FROM Courses WHERE (CPTRequired = 'true') AND (CourseID NOT ...

exception in Linq to sql

my query is : var ReadAndUnreadMessages = (from m in MDB.Messages orderby m.Date descending where m.ID_Receive == (Guid)USER.ProviderUserKey && m.Delete_Admin == false select new AllMessages() { id = (LoadMessageChildren(m.ID_Message)[LoadMessageChildren(m.ID_Message).Count - 1] a...

Best way to take advantage of .NET 3.5

I currently have an asp.net web application which is using seperate assemblies for the data access, the business logic, entity objects, and the web user interface. The data access was created using Microsoft's Data Access Application Block compilied as a .NET 2.0 assembly. Stored procedures were used for the actual moving of data in and ...

How do I Programmatically change Connection String un LINQ C# Winforms

Hey Guys. This question may be redundant, but i could not totally understand how to do this. I need to be able to, when the users Run my Winforms app, can search for the instance of SQL if the previous one are not available. I already have the check for the DB existence, and also made a dialog wich search for all the available instance, ...

How do I get a list of column name using Linq to Sql

How can i dynamically get a list of Column names based on my query model below using Linq to SQL. Example of query below. public void GetColumnName() { var db = from ci in Db.CatalogItems join i in Items on ci.ItemId equals i.ItemId select i; } ...

Linq To Sql and WPF : DeferredLoadingEnabled = false and DataBinding

After using a global Linq to Sql DataContext and having unwanted inserts, I decided to use a detached methodology. I did this through a methods class that serves as a abstraction layer from my buisness objects to my DAL. There is one DataContext per each CRUD call so I can call something like this. using (DatabaseMethods<TEntity> db = ...

Conversion from string "" to type 'Integer' is not valid.

When I try to run the following code I get a Conversion from string "" to type 'Integer' is not valid. error. Dim maj = (From c In connect.Courses _ Where c.COTRequired = CBool("True") _ Select c.CourseID, c.CourseName, c.CreditHours).Except _ (From en In connect.Enrollments ...

Linq2SQL or SQL: Find dates that don't have events

What would be the best way to find dates that dont have events in a given time interval given that different events can overlap, span multiple days, start before the interval, and end after the interval. ie: event start end e1 01/01/2009 02/01/2009 e2 01/15/2009 01/31/2009 e3 08/15/2008 01/16/2009 e4 ...

SQL Compact - Hang on invalid file

I use Linq to Sql Compact with code generated by sqlmetal. When I open an invalid database, like eg an mp3, I can create the DataContext without getting an Exception. But when I query any data or call DatabaseExists, the application hangs. Why is no exception thrown? Is this a known problem? ...