LINQ to Sharepoint InsertOnSubmit Question

Question

For example I have a list called Product and it has 3 columns, ProductName (which is the Title), ProductPrice and ProductType.

• ProductName is a string
• ProductPrice is a currency (double)
• ProductType is a LookUp on ProductTypes List

Normally this is easy for me if it does not contain a LookUp column, but I dont know how to deal with look up columns when Inserting.

I had tried this one but it returns an error Specified cast is not valid.

Here is the current code

EntityList<ProductTypeItem> ProductTypes = dc.GetList<ProductTypeItem>("ProductType");

ProductItem newProduct = new ProductItem();

newProduct.Title = txtProductName.Text;
newProduct.ProductPrice = double.Parse(txtProductPrice.Text); 
newProduct.ProductType = (from a in ProductTypes where a.Title == ddProductType.SelectedItem.Text select a).FirstOrDefault();

dc.Product.InsertOnSubmit(newProduct);
dc.SubmitChanges();   

What would I do with the newProduct.ProductType as here is where the error occurs.

Please note that the ddProductType DataSource is the ProductType List and uses Title in its DataTextField and DataValueField

Answer 1

It works now and here is the solution

EntityList<Item> ProductTypes = dc.GetList<Item>("ProductType");
Item oProductType = (from a in ProductTypes where a.Title == ddProductType.SelectedItem.Text select a).FirstOrDefault();
ProductItem newProduct = new ProductItem();

newProduct.Title = txtProductName.Text;
newProduct.ProductPrice = double.Parse(txtProductPrice.Text);
newProduct.ProductType = (ProductTypeItem)oProductType;

dc.Product.InsertOnSubmit(newProduct);
dc.SubmitChanges();   

The only thing I changed is to initialize the the Product Type as Item rather than ProductTypeItem, then cast it to ProductTypeItem now it works

Answer 2

The way you are doing seems correct to me. It is the same way I've been doing it with success. Are you sure the problem is the lookup column? Is double the correct type for the currency? Often currency is saved as a decimal, not a double.

By the way, you don't have to get the Entitylist separately. SPMetal makes the shorthand dc.ProductType, like the one you already use in the insertOnSubmit. No idea why all examples do this...

Try to split the assignments a bit and debug again. See if everything is the way it should be.

ProductItem newProduct = new ProductItem();
string selectedProductType = ddProductType.SelectedItem.Text;
ProductTypeItem productType = (from a in dc.ProductType
                               where a.Title == selectedProductType
                               select a).FirstOrDefault();

newProduct.Title = txtProductName.Text;
newProduct.ProductPrice = decimal.Parse(txtProductPrice.Text); 
newProduct.ProductType = productType;

dc.Product.InsertOnSubmit(newProduct);
dc.SubmitChanges();

Hope this helps

Answer 3

This might help you out. The first example explains how the insert should work with links to existing data. This sample code should give you enough hints to help you fix your problem:

AdventureWorksDataContext db = new AdventureWorksDataContext();

// LINQ query to get StateProvince
StateProvince state = (from states in db.StateProvinces
                       where states.CountryRegionCode == "AU" && states.StateProvinceCode == "NSW"
                       select states).FirstOrDefault();
// LINQ function to get AddressType
AddressType addrType = db.AddressTypes.FirstOrDefault(s => s.Name == "Home");

Customer newCustomer = new Customer()
{
    ModifiedDate= DateTime.Now,
    AccountNumber= "AW12354", 
    CustomerType='I',
    rowguid= Guid.NewGuid(),
    TerritoryID= state.TerritoryID    // Relate record by Keys
};
Contact newContact = new Contact()
{
    Title = "Mr",
    FirstName = "New",
    LastName = "Contact",
    EmailAddress = "[email protected]",
    Phone = "(12) 3456789", 
    PasswordHash= "xxx",
    PasswordSalt= "xxx",
    rowguid = Guid.NewGuid(),
    ModifiedDate = DateTime.Now
};
Individual newInd = new Individual()
{
    Contact= newContact,    // Relate records by objects (we dont actually know the Keys for the new records yet)
    Customer= newCustomer,
    ModifiedDate= DateTime.Now
};
Address newAddress = new Address()
{
    AddressLine1= "12 First St",
    City= "Sydney",
    PostalCode= "2000", 
    ModifiedDate=DateTime.Now,
    StateProvince= state,
    rowguid = Guid.NewGuid()
};

// Link our customer with their address via a new CustomerAddress record
newCustomer.CustomerAddresses.Add(new CustomerAddress() { Address = newAddress, Customer = newCustomer, AddressType = addrType, ModifiedDate = DateTime.Now, rowguid = Guid.NewGuid() });

// Save changes to the database
db.SubmitChanges();

Answer 4

I'm not familiar with Linq to SharePoint, but I assume it is similar to the Client Object model. If so, you'll need to use a FieldLookupValue for the value of newProduct.ProductType and use the ID of the lookup as the value:

newProduct.ProductType = new FieldLookupValue { LookupId = 1 };

This means you'll need to have access to the lookup value's ListID in your ProductTypes query.

Answer 5

The LINQ to SharePoint generated code is out of sync with you list. Re-generate the list and it will work -Paul Beck