How to add data using linq to entities in relational table?

Question

This is my database schema.

Delivery(LRno(PK), Destination, date, TransporterID(FK), Weight, TruckNo)
Transporter(TransporterID(PK), TransporterName)

Now i have following controls in UI:

LRNo, Destination, Date, Weight, TransporterName.

I would like to insert TransporterID(FK) in Delivery table when user provide input as transporter name. User is supposed to select transporter name while TransporterID should be inserted in delivery table. No changes were made in Transporter table.

We are using linq to entities.I am new to EF and LINQ.

rDatabaseEntities context = new rDatabaseEntities();
        Delivery del = new Delivery();
        del.TruckNo = trucknotxt.Text;
        del.LR_No = Convert.ToInt32(lrnotxt.Text);
        var query = from t in context.Transporters where t.transporterName == transcombo.SelectedItem select t.TransporterID;
        context.Deliveries.AddObject(query);

transcombo is combobox to select transporter name.

I am getting following two errors for last line (context.Deliveries.AddObject(query);)

1) The best overloaded method match for 'System.Data.Objects.ObjectSet<linqproj.Delivery>.AddObject(linqproj.Delivery)' has some invalid arguments

2) Argument 1: cannot convert from 'System.Linq.IQueryable<int>' to 'linqproj.Delivery

What to do next ?

goobering · Answer 1 · 2015-03-23T13:59:50.690

You will, I think, have to select a single result of the right type from your query:

var query = (from t in context.Transporters where t.transporterName == transcombo.SelectedItem select t.TransporterID).Single();

At this point I'm unsure whether this is doing what you intend it to. The above query will produce an instance of whatever TransporterID is. If it's an ID then I'd imagine it should be an int or a guid? You're then trying to add that object to your context as a Delivery:

context.Deliveries.AddObject(query);

Looks odd to me. If, however, TransporterID is somehow a Delivery object then you should be able to cast it to the right type and add it to the context like this:

context.Deliveries.AddObject(query as Delivery);

score 0 · Accepted Answer · answered Mar 23 '15 at 13:57

0

The problem is "query" is a collection of int values and contains all the ids of transporters that satisfy the condition. In your case i'm guessing there shouldn't be two transporter with the same name, so take the first result, or null if no transporter with that name. Also you need to assign that transporter to the delivery and add the delivery to the context, not the transporter.

    rDatabaseEntities context = new rDatabaseEntities();
    Delivery del = new Delivery();
    del.TruckNo = trucknotxt.Text;
    del.LR_No = Convert.ToInt32(lrnotxt.Text);
    Transporter transporter = (from t in context.Transporters where t.transporterName == transcombo.SelectedItem select t).FirstOrDefault();
    if(transporter != null)
    {
        del.Transporter = transporter;
    }
    context.Deliveries.AddObject(del);

answered Mar 23 '15 at 13:57

Maria

81
7

The solution does not give error,but transporterID is not inserted in delivery table. Here is my code `var selval = transcombo.SelectedItem.ToString(); trans = (from t in context.Transporters where t.transporterName == selval select t).FirstOrDefault();` trans is transporter object already declared earlier. – Gaurav Chauhan Mar 24 '15 at 11:49
Put a breakpoint and see if your trans object is null. Maybe there's no transporter with that name in your db. – Maria Mar 24 '15 at 12:53
This thing worked... http://stackoverflow.com/questions/4902039/difference-between-selecteditem-selectedvalue-and-selectedvaluepath... – Gaurav Chauhan Mar 25 '15 at 11:32

How to add data using linq to entities in relational table?

2 Answers2