Use Dynamic LINQ Library with join

问题:

Following is the UI :

And this is code snippet i am using to fire dynamic where clause :

public void bind()
{
 string filter = "";
            if (!string.IsNullOrEmpty(txtPart.Text))
            {
                 filter = filter + "masterinv.inv_item_id = " + txtPart.Text;
            }
            if (!string.IsNullOrEmpty(txtDescription.Text))
            {
                if (!string.IsNullOrEmpty(filter))
                {
                    filter = filter + " || masterinv.description = " + txtDescription.Text;
                }
                else
                {
                    filter = filter + "masterinv.description = " +  txtDescription.Text;
                }


            }
            if (!string.IsNullOrEmpty(txtVendor.Text))
            {
                if (!string.IsNullOrEmpty(filter))
                {
                    filter = filter + " || vendor.vendor_name = " + txtVendor.Text;
                }
                else
                {
                    filter = filter + "vendor.vendor_name = " +  txtVendor.Text;
                }

            }
 InventoryDataContext dc = new InventoryDataContext(InventoryDBContext.GetConnectionstring());
                var searchResult = (from masterinv in dc.OMS_REF_Master_Inventories 
                                   join vendor in dc.OMS_REF_Vendors on masterinv.inv_item_id equals vendor.inv_item_id
                                   Where(filter)
                                   select new OMS_REF_Master_Inventory
                                    {
                                        inv_item_id = masterinv.inv_item_id,
                                        description = masterinv.description,
                                        unit_of_measure = masterinv.unit_of_measure,
                                        lot_id = masterinv.lot_id,
                                        serial_id = masterinv.serial_id,
                                        mfg_id = masterinv.mfg_id,
                                        mfg_item_id = masterinv.mfg_item_id,
                                        item_status_current = masterinv.item_status_current,
                                        cm_unit_cost = masterinv.cm_unit_cost,
                                        sync_dte = masterinv.sync_dte
                                    }).ToList();
                     searchResult;
 }

In the above code filter created on the basis of combination of combo box and text field selection.

Out of these one filter is :

masterinv.inv_item_id  = 'A' || masterinv.description = 'F' || vendor.vendor_name = 'V'

it may vary depend upon the combobox value selection. All cases of Combo box present in BuildQueryFilter Method.

PROBLEM :

I am not able to fire where clause in this join. Where i am going wrong ?

回答1:

I think you cannot use those % with linq queries.

Instead of % you can use Contains()/StartsWith()/EndsWith()

refer this for more info...

How to do SQL Like % in Linq?

How to do a LIKE query with linq?

Or

Use Sql Methods..

where SqlMethods.Like(c.CustomerName, "%/abc/%")