.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

LINQ TO SQL question

Posted By:      Posted Date: October 01, 2010    Points: 0   Category :ASP.Net

Background: I took over a project that I have to upgrade and I had a performance/suggestion question for the group.  I have a table of customers {id, name} and a table of contact information {id, fkcustomersid, data, type, default}.

Each record in the contactinformation table has a realtionship with the customers table (fkcustomerid), either an email or phone number (data), A type describing what is stored in the data column 'Email' or 'Phone' (type), and 1 or 0 denoting whether this contact record is their default email or default phone (default). 

I need to pull back a grid that in each row has the customers name, their default email, and their default phone. 

The problem I am running into is that the customer can have 0 or many emails and 0 or many phone numbers, so I have not been able to come up with a join statement (or simple linq query that only queries the database once) to return one row containing the customerID, customer name, default email (if available), default phone number (if available). 

Right now, I have this setup in linq like this:

var customers = from c in db.tblCustomers
                        select c;

foreach (var customer in customers)
     dr[0] = customer.ID;
     dr[1] = customer.Name;
     var defaultPhone = db.tblContactInfo.SingleOrD

View Complete Post

More Related Resource Links

C#, LINQ: (List<>) Question???


Hi Everyone,

I have a strange problem:


There is an entity (Table) called "Filters" in my .edmx which has all my tables and stuff in it (to use it for LINQ queries)

I have created a class called "CustomFilters" which inherits "Filters". So now my cutom class has all the properties of the original "Filters" class (table) and 1 more additional property that i wanted which is a List of "CustomFilters":

List<CustomFilters> children = new List<CustomFilters>();

Now i get all my records into:

List<CustomFilters> theOriginalList = new List<CustomFilters>();

NOTE: first all records are tken into a List<Filters> and then these are added into List<CustomFilters> where List<CustomFilters> for children of that record is added, and then children of the child records, and so on......

so now, every record is an instance of CustomFilters which has a property named "children" which is a list again; and each child again has instanaces of "CustomFilters", and each instance again has children...and on and on and on.....

(basiacally its a data source for a treeview in my silverlight control)

every thing is good and working as expected.


Noob Question - Using Linq in View

I just inherited an MVC2 application. So far my only experience with MVC is reading the Nerd Dinner sample chapter and downloading the nerd dinner app. I do plan to dive much deeper but i need a quick answer because i have to hit the ground running. So basically in the main Index view the view model is being passed as an IQueryable. Then he is doing some queries on the view model to loop through the data. See example below:<% if (Model.Where(w => w.State == workflowStep.Key).Count() > 0) { %>To me, even though minor, this is business logic and shouldn't be in the view. Shouldn't any type of logic like this be sorted out before you get to the view? Or is this common practice in MVC?Basically i want to know if I should spend time refactoring this thing or just go with the flow.  Thanks.

Linq to SQL - DeferredLoadingEnabled/DataLoadOptions Question

I am reading a book that suggests that when you need data from a child table that you should both: Set DeferredLoadingEnabled to false and Set the DataLoadOptions This seems a bit redundant to me.  If DeferredLoadingEnabled is false, why would you need to set the DataLoadOptions?  Doesn't turning off deferred loading mean that the child tables will automatically get loaded when the query is executed? Thanks

LINQ left outer join question

I can do this in other ways, but am looking for the "LINQ" way of doing this: Table1: People (PersonId,SchoolId)Table2: AssignedPeople (PersonId,SchoolId,RoomId) I want to select the people in Table1 within a particular SchoolId that are not in the assignedPeople table (in a particular Room).  The SQL is: select p.* from people p left outer join assignedpeople ap on p.PersonId=ap.Person and p.SchoolId=ap.SchoolIdand ap.RoomId=@roomId where p.SchoolId=@SchoolId and ap.PersonId is null I tried this LINQ (VB,NET):dim SchoolID as guid=...dim RoomID as guid=... (from p in dc.people where p.SchoolId=SchoolIdgroup join ap in dc.AssignedPeopleon p.PersonId equals ap.PersonId and p.SchoolId equals ap.SchoolId into groupfrom g in group.DefaultIfEmptywhere g.RoomID = nothing) but I can't get the ap.RoomId=@roomId part into the right place, should be something like:group join (from ap in dc.AssignedPeople where ap.RoomId=RoomId) Any help would be appreciated,Reuven

Simple question.... LINQ on Datatable

Hi all,Simple one this I am sure for those who use LINQ! :)I basically have two tables that will be stored in viewstate until I save the data to the database.  I need a way to query the datatables, delete remove etc.  Is this possible in LINQ?So for example, delete/select rows from the DataTable that have a particular ID etc...Thanks,Mark

Question on WCF service with Linq to SQL on 64-bit system

I'm trying this question one more time since I realized the work in in a 64-bit environment. I have a WCF service which runs in production in a 32-bit environment using VS2008 and .NET 2.0. We made a big change to our environment (lots at the same time) and I moved the services to VS2010, .NET 4.0, and 64-bit processes.  I wrote a new service and deployed it under IIS.  If I browse to the service I get the example screen and clicking on the link gives me the metadata.  So all of that is working. In my services I create a Linq to SQL context, perform a query, then return the result set as an Array.  I wrapped the context creation with a using.  My tests returned an exception "Attempt to access an object that has already been disposed".  I traced it down and found that the code executes, forces the query, returns the results, exits the using statement (disposing the context properly) then exits the method.  The next thing I get is the exception, inside framework code, and it is linq code attempting to fulfill the query once again. I have searched until I'm blue in the face trying to find prior incidents and fixes but all I found was one post, two years ago, same problem with less debugging, and no answer to the problem other than 'don't use the using statement' which is a bad suggestion.  So for a work around I wrote a Co

Simple LINQ to SQL question


Someone help me with what I'm missing here...

I have a VERY simple file as you can see... just three tables.


The Problem is that if I do this somethign like this....

var patients = from x in db.CT_Patients
                     where x.CT_Visits...... (error here)


It doesn't display child properties for CT_Visits from CT_Patient like it should. I only get the standard LINQ queries like Select, Where, Orderby etc... What am I missing???


data shaping question - can this much conditional processing can be done within the linq query?



I have a scenario where I need to map the value of a field to a different value... easier explained with simple example:

when actual field data is "RED" I need to return "some other data"
when actual field data is "BLUE" I need to return "something else" 

so I've written linq to sql statements that project and shape, but I've never had to include any 'conditional' processing, can you?

Linq Question - Calculating Value for Order By



I have a class that I have written to calculate distance base on Lat/Lng. In my linq I need to calculate this distance and order by it. Something along these lines?

 IEnumerable<TheEntity> data = from ent in entities.TheEntitys
                                                  let geo = new GeoHelperClass()
                                                  join loc in entities.TheEntitysLocations on ent.ID equals loc.EntID
                                                  orderby geo.CalcDistance(loc.Lat, loc.Lng, reqLat, reqLng)
                                                  select ent;

This is what I origanally thought of but clearly not.

Any help on this would be apprechiated.


LINQ Newbie question



I am playing around with LINQ and wondering how difficult or easy it would be to convert some of my SQL stored procedures to LINQ.  Would this be straight forward to convert?

ALTER procedure [dbo].[nfl_fixture_list_Test]
    @season varchar(50) ,
    @team varchar(50) 
Select    [Week] As Week,
        Case When HomeTeam = @team
             Then upper(AwayTeam)
             Else HomeTeam
             End As Opponent,
        Case When HomeTeam = @team
             Then Convert(VarChar(20), IsNull(HomeScore, '')) + '-' + Convert(VarChar(20), IsNull(AwayScore, ''))
             Else Convert(VarChar(20), IsNull(AwayScore, '')) + '-' + Convert(VarChar(20), IsNull(HomeScore, ''))
             End As Result,
        '../../../content/images/logos/' + replace(Case When HomeTeam = @team Then AwayTeam Else HomeTeam End, ' ', '') + '.png' As Image
From     nfl_fixtures
Where    Season = @season
        And (AwayTeam = @team Or HomeTeam = @team)
Order By [Week]


C# Linq Threshold Question


Good Afternoon everyone,

I've been trying to figure out the most efficient way to do this, but am falling short. Here's how it goes...

I am ultimately trying to determine "like customers" based on a specific customer's buying habits and a given threshold, say 50%. IE customer 1 purchased products A,B,C,D  ... customer 2 purchased B,C,D,E ... these two customers are >= 50% "likeness" so they should be matched.

My schema is as would be expected

CLIENT (1 ----- many)  CLIENT_PURCHASE (1 -------many) PRODUCT

*clientID                      *clientID *prodID                            *prodID

For now I am ignoring the threshold and simply am trying to find customers who have purchased any item within customer 1's history via a linq subquery. I think I have this working with the following two queries:

var clientOneHistory = (from cp in client.Client_Purchase
select cp.prodID).ToList();

var matchedClients = (from cp in db.Client_Purchase

A very simple question on the LINQ Count function, but I can't figure it out....


Hi guys, I think this must be really simple but for some reason I can't get this to work.  I have an arraylist of stock ticker names and the same names can appear multiple times in the list.  I am trying to create a LINQ query to find which ticker names appear a certain number of times in the list. 

The arraylist is like: {Dell, Citi, Appl, Appl, Citi, Msft, Dell, Appl, Dell}

If I want to find which tickers appear 3 times in the list, which is named "stocklist", my current query is:


Dim newquery = From ticker In stocklist _

Where ticker.count = 3 _

Select ticker


But the query keeps giving me an error when I try to run it.  I'd greatly appreciate if someone could please let me know how to correct this problem.  Thanks in advance!

MVC Linq Join List Question


This is the query I have

    Function Index() As ActionResult

            ViewBag.dateNow = DateTime.Now

            Dim result = (From newLocation In DataAccess.Locations Join
                         newState In DataAccess.States
                         On newLocation.state Equals newState.id
                         Select newLocation, newState)

            '   Return View(DataAccess.Locations.ToList)
            Return View(result.tolist)

        End Function

I am trying to populate the ToList View, but I can't figure out how to call a specific column since it is using a join...

Example:   @item.address

I was thinking it would be like   @item.newlocation.address

However, that is not working --- any advice?

Playing with Linq grouping: GroupByMany?

One of its features is grouping. Many people understand grouping like it is defined in Sql. Linq is implementing grouping quite the same way. Let's discover this syntax and how to make consecutive groups easier.

LINQ : Implementing IN and NOT IN

I got tried of typing

var result = from s in source
where items.Contains(s)
select s;and so I implemented the IN and NOT IN methods as extension methods:

101 LINQ Samples Tutorials

101 LINQ Example with sample code snippets....This sample prints each element of an input integer array whose value is less than 5. The sample uses a query expression to create a new sequence of integers and then iterates over each element in the sequence, printing its value

Linq: how to share parameters between lambda expressions ?

When using Linq to objects, you will quickly feel the need to pass some parameters from a method to another but it's not so easy because each Linq method is not calling the following one. In a Linq sequence, each method is using the result computed by the previous one. So, local contexts are not visible from one method to another.
The compiler is using two technical different ways to let parameters go out of a method.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend