>In your example all records would be returned anyway - it's just a question of where the sorting takes place.
>
>Linq to Sql filtering such as .Where() should be translated to the T-SQL equivalent so you won't be getting all records so I'm not sure what type of statements you are referring to?
>Can you give an example of your problem showing the linq query and the T-SQL execution ?
>
>
Here is one of the samples

 var emails =
                _contactsEmailAddressesLinkAdapter.GetAll()
                    .Where(e => e.ContactId == contacts.ContactId)
                    .ToList();

When I look into profile, I see that it just runs select of all rows from the table. So, filtering is done on the client as opposed to SQL Server.

I am going to write 3 stored procedures right now to return the information I need the right way in one select statement. Because currently this line of code follows a loop

var emailList = new List<EmailAddresses>();
            if (emails.Count > 0)
            {
                var emailId = contacts.PrefEmId;

                var tempEmailList = new List<EmailAddresses>();
                foreach (var email in emails)
                {
                    var em = _emailAddressesAdapter.GetById(email.EmId);
                    em.EmailAddressTypes = _emailAddressTypesAdapter.GetById(em.EmTypeId);
                    if (contacts.PrefEmId == email.EmId)
                    {
                        // First in the array
                        emailList.Add(em);
                    }
                    else
                    {
                        tempEmailList.Add(em);
                    }
                }
                emailList.AddRange(tempEmailList);
            }
            contacts.EmailList = emailList;

Instead of this highly inefficient code I'll write a statement returning right info and in the right order. I can do it using direct SQL statements, but I think SQL procedure may be a better choice.