Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator

code:
var cpList2 = (from customerProduct in _context.customer_products
join controlPolice in controlPolices
on customerProduct.sales_ID equals controlPolice.sales_ID
where customerProduct.yenileme_no == controlPolice.yenileme_no
&& customerProduct.zeyil_no == controlPolice.zeyil_no
select customerProduct);

error:
Local sequence cannot be used in LINQ to SQL implementation of query
operators except the Contains() operator

solution:
var cpList = (from controlPolicy in controlPolicies
join customerProduct in _context.customer_products
on controlPolicy.sales_ID equals customerProduct.sales_ID
where controlPolicy.yenileme_no == customerProduct.yenileme_no
&& controlPolicy.zeyil_no == customerProduct.zeyil_no
select customerProduct);

source:
http://stackoverflow.com/questions/5936301/local-sequence-cannot-be-used-in-linq-to-sql-implementations-of-query-operators-e

linq clone error

error:

Object graph for type 'Uye.UyeMacAdresi' contains cycles and cannot be serialized if reference tracking is disabled.

solution:

in dbml file change the 'Serialization Mode' to 'Unidirectional'

linq - Row not found or changed

trouble:

var daskTeklif = _context.tblDaskTeklifs.SingleOrDefault(dt => dt.teklif_id == TeklifTkID);
daskTeklif.SirketPoliceNo = policeNo;
_context.SubmitChanges();

throws an exception: System.Data.Linq.ChangeConflictException: Row not found or changed.

solution:

creating a new datacontext:

var context = new DataClassesDataContext();
daskTeklif = context.tblDaskTeklifs.SingleOrDefault(dt => dt.teklif_id == TeklifTkID);
daskTeklif.sirketPoliceNo = policeNo;
context.SubmitChanges();

cycle error on cloning linq object

error: "Object graph for type 'TestLinq.PersonAddress' contains cycles and cannot be serialized if reference tracking is disabled.”

solution: change the 'Serialization Mode' to 'Unidirectional'.

source: http://stackoverflow.com/questions/1432501/clone-linq-object-error-object-graph-for-type-testlinq-personaddress-contains

clone linq object

///

/// Clones any object and returns the new cloned object.
///

/// The type of object.
///
The original object./// The clone of the object.
public static T Clone(T source)
{
var dcs = new DataContractSerializer(typeof(T));
using (var ms = new System.IO.MemoryStream())
{
dcs.WriteObject(ms, source);
ms.Seek(0, System.IO.SeekOrigin.Begin);
return (T)dcs.ReadObject(ms);
}
}

source:http://stackoverflow.com/questions/2178080/linq-to-sql-copy-original-entity-to-new-one-and-save

anonymous type in repeater or datalist

rptTargets.DataSource = from t in DB.SalesTargets select new { t.Target, t.SalesRep.RepName };

string repName = (string)DataBinder.Eval(e.Item.DataItem, "RepName");
string target = (string)DataBinder.Eval(e.Item.DataItem, "Target");

http://stackoverflow.com/questions/1212210/anonymous-type-in-repeater-databound-event

linq ExecuteQuery return multiple objects

// 1. Sadece Table1 sınıfını döndürür:

var query = "Select t1.*, t2.*, t2.PK
From Table1 t1
Inner Join Table2 t2 On t2.PK= t1.FK"


// 2. Hem Table1 sınıfını, hem de Table1 sınıfını döndürür:

var query = "Select t1.*, t2.*, t1.FK
From Table1 t1
Inner Join Table2 t2 On t2.PK= t1.FK"

var karars = db.ExecuteQuery(query).ToList();


// 3. Herhangi bir sınıf'ın örneğini döndürmez. Sadece select ile belirtilen alanları döndürür:

select new { k.KararId, k.Baslik, k.EsasNo, k.EsasTarihi, k.KararNo, k.KararTarihi, k.Ozet, k.MerciId, m.MerciAdi }).Distinct();


protected string GetMerciAdi()
{
try
{
// durum 2
return Eval("MerciAdi").ToString();
}
catch (Exception)
{
// durum 3
return ((Merci)Eval("Merci")).MerciAdi;
}
}

extension methods in linq

public static class MyExtensions
{
    public static IEnumerable SelectWhere(
     this IEnumerable source,
     Func filter)
    {
        var results = new List();

        foreach (var s in source)
            if (filter(s))
                results.Add(s);

        return results;
    }
}


    private double? GetConvertedPrice(RealEstate realEstate)
    {
        return realEstate.Price * exchangeRates[realEstate.PriceCurrencyId.Value - 1].Value;
    }




        var realEstates = _db.RealEstates.SelectWhere(re =>
                                                       (re.CategoryId == null || catId == null || re.CategoryId == catId)
                                                       && (re.TypeId == null || typeId == null || re.TypeId == typeId)
                                                       && (re.CityId == null || cityId == null || re.CityId == cityId)
                                                       && (re.DistrictId == null || districtId == null || re.DistrictId == districtId)
                                                       && (re.Price == null || priceMin == null || GetConvertedPrice(re) >= priceMin)
                                                       && (re.Price == null || priceMax == null || GetConvertedPrice(re) <= priceMax)
            );


http://msdn.microsoft.com/en-us/library/bb383977.aspx

linq : left join with linq

            var vehicles = (from vehicle in DB.Vehicles
                            join brand in DB.VehicleBrands on vehicle.BrandId equals brand.Id into data_b from b in data_b.DefaultIfEmpty()
                            join model in DB.VehicleModels on vehicle.ModelId equals model.Id into data_m from m in data_m.DefaultIfEmpty()
                            join type in DB.VehicleTypes on vehicle.TypeId equals type.Id into data_t from t in data_t.DefaultIfEmpty()
                            where
                                (brandId == null || vehicle.BrandId == brandId) &&
                                (modelId == null || vehicle.ModelId == modelId) &&
                                (typeId == null || vehicle.TypeId == typeId) &&
                                (priceMin == null || vehicle.Price >= priceMin) &&
                                (priceMax == null || vehicle.Price <= priceMax) &&
                                vehicle.IsDeleted == false && vehicle.IsActive == true && vehicle.IsSold == false
                            orderby vehicle.CreateDate descending
                            select new
                                       {
                                           vehicle.Id,
                                           vehicle.Title,
                                           b.BrandName,
                                           m.ModelName,
                                           vehicle.Price,
                                           vehicle.CreateDate,
                                           vehicle.PhotoPath
                                       });

            gvVehicle.DataSource = vehicles;
            gvVehicle.DataBind();

linq deleteonnull

An attempt was made to remove a relationship between a User and a User_UserRole. However, one of the relationship's foreign keys (User_UserRole.UserID) cannot be set to null.

[Association(Name="User_User_UserRole", Storage="_User", ThisKey="UserID", OtherKey="UserID", IsForeignKey=true, DeleteOnNull = true)]

Cannot remove an entity that has not been attached

You are using two different context. One retrieves it, the second tries to delete it.

http://forums.asp.net/p/1475529/3430701.aspx#3430701

linq : insert into middle table

var band = new Band {Title = bandName};
if (_db.Bands.Where(b => b.Title == bandName).Count() == 0)
{
_db.Bands.InsertOnSubmit(band);
_db.SubmitChanges();
}

1. sadece band_genre tablosuna kayıt yapar:

var bandGenre = new Band_Genre();
bandGenre.BandId = band.BandId;
bandGenre.GenreId = _currentGenre.GenreId;
_db.Band_Genres.InsertOnSubmit(bandGenre);
_db.SubmitChanges();


2. band_genre, band ve genre tablolarının tümüne kayıt yapar. (yani aynı band ve genre birden fazla kez eklenebilir.):

var bandGenre = new Band_Genre {Band = band, Genre = _currentGenre};
_db.Band_Genres.InsertOnSubmit(bandGenre);
_db.SubmitChanges();

System.NotSupportedException: An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext

System.NotSupportedException: An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext. This is not supported.

You may have gotten this error when using LINQ to SQL. I personally hate this error, but surprisingly enough, out of 2 years of using LINQ to SQL, this is the first time I've gotten this (2 years later, I mean). This error happens in this scenario: imagine loading up an object Order. This object has a primary key relationship Customer, which you can access via:

Order.Customer

Under the scenes, the order class uses the EntityRef class to store the order. It's default value is default(EntityRef), which is it's default state (existing EntityRef reference, but null Customer reference within this class so no value gets assigned). Say you are accessing the customer in an order that already exists. When you do:

order.Customer

This reference gets loaded from the context, using the value from CustomerKey. if you assign a new value to Order.CustomerKey as in:

order.CustomerKey = 3;

The LINQ entity notices that the Customer object (underlying EntityRef _Customer variable) has already been loaded (through its HasLoadedOrAssignedValue property, or similarly named). Because you are now changing the key, this exception gets thrown because LINQ is setup not to allow you to do this. The way to change this is by doing:

order.Customer = this.DataContext.Customers.First(i => i.Key == 1);

You have to assign the reference to the Customer, and this instead updates the key, the reverse way around. I don't like this because this forces me to perform an additional data query, and in a business app this may reduce performance just to update a reference. I chose an alternate way. By adding a partial class for the order, I add a ClearCustomer method that does the following (note all LINQ types implement partial keyword, which means you can create another partial class to add additional methods).

public partial class Order ...
{
public void ClearCustomer()
{
_Customer = default(EntityRef);
}
}

And now, calling this method before changing the assignment clears the customer, and I can make the change. A bit of a hack, but it works. Unfortunately, because again the EntityRef is internal to the class, the method has to be in the class definition (can't use a static helper class to do this, at least easily). To update the reference, I can now do:

order.ClearCustomer();
order.CustomerKey = 7;

http://dotnetslackers.com/Community/blogs/bmains/archive/2009/05/21/linq-to-sql-error-foreignkeyreferencealreadyhasvalueexception.aspx

recursive function

private void BindMenu()
{
if (TreeView1.Nodes.Count == 0)
{
IntertechDBDataContext db = new IntertechDBDataContext();
var pages = db.Pages;

foreach (Page page in pages.Where(p => p.ParentPageID == null))
{
TreeNode node = new TreeNode();
node.Value = page.PageID.ToString();
node.Text = page.Title;
node.NavigateUrl = "~/Admin/Page/Details.aspx?id=" + node.Value;
TreeView1.Nodes.Add(node);

BindChilds(node, page.Pages);
}
}

private static void BindChilds(TreeNode parentNode, EntitySet childs)
{
if (childs.Count > 0)
{
foreach (Page page in childs)
{
TreeNode node = new TreeNode();
node.Value = page.PageID.ToString();
node.Text = page.Title;
node.NavigateUrl = "~/Admin/Page/Details.aspx?id=" + node.Value;
parentNode.ChildNodes.Add(node);

BindChilds(node, page.Pages);
}
}
}