Re: C# Linq To SQL Dynamic Where Clause - C# Discussion Boards

20-Apr-14 11:39

I will have a couple of methods that are called to mark records as deleted:

public DeleteByAddressIdResponse DeleteByAddressId(int Id)
{
    Func<ClientAddressEntity, bool> whereClause = ca => ca.AddressId == Id;

    return deleteClientAddresses<DeleteByAddressIdResponse >(whereClause);
}


public DeleteByAddressTypeIdResponse DeleteByAddressTypeId(int Id)
{
    Func<ClientAddressEntity, bool> whereClause = ca => ca.AddressTypeId == Id;

    return deleteClientAddresses<DeleteByAddressTypeIdResponse >(whereClause);
}


public DeleteByClientIdResponse DeleteByClientId(int Id)
{
    Func<ClientAddressEntity, bool> whereClause = ca => ca.ClientId == Id;

    return deleteClientAddresses<DeleteByClientIdResponse >(whereClause);
}

I'm trying to create a where clause for a Linq To Sql query. I'm not sure how to apply there where clause.

private T deleteClientAddresses<T>(Func<ClientAddressEntity, bool> whereClause) where T : _ResponseBase
{
    var response = (T)Activator.CreateInstance(typeof(T));

    using (var dc = getDataContext())
    {
        // ============>  HOW DO I APPLY THERE WHERE CLAUSE HERE??? <=============
        var entities = (from ca in dc.ClientAddresses select ca);

        foreach (var entity in entities)
        {
            var deleteAddressResponse = DeleteAddress(entity.AddressId);

            if (deleteAddressResponse.Result == Result.Success)
            {
                entity.IsDeleted = true;

                try
                {
                    dc.SubmitChanges();
                }
                catch (Exception e)
                {
                    response.Exception = e;
                    break;
                }
            }
            else
            {
                response.Exception = deleteAddressResponse.Exception;
            }
        }
    }

    return response;
}

Thanks

If it's not broken, fix it until it is

Richard Deeming22-Apr-14 2:07

Richard Deeming

22-Apr-14 2:07

You need to call the Where extension method[^] using the method syntax instead of the query syntax.

However, since you're using a Func<T, bool> to filter the records, the filter cannot be translated to a SQL query. As a result, all records will be loaded into memory before being passed to the filter to see if they match. If you change your code to use an Expression<Func<T, bool>>, the filter will be passed to SQL, and only the matching records will be returned.

You can also avoid the Activator.CreateInstance call by adding the new() constraint to your generic type parameter.

private T deleteClientAddresses<T>(Expression<Func<ClientAddressEntity, bool>> whereClause) where T : _ResponseBase, new()
{
    var response = new T();
 
    using (var dc = getDataContext())
    {
        var entities = dc.ClientAddresses.Where(whereClause);
 
        foreach (var entity in entities)
        {
            ...
        }
    }
 
    return response;
}

public DeleteByAddressIdResponse DeleteByAddressId(int Id)
{
    Expression<Func<ClientAddressEntity, bool>> whereClause = ca => ca.AddressId == Id;
    return deleteClientAddresses<DeleteByAddressIdResponse>(whereClause);
}
 
public DeleteByAddressTypeIdResponse DeleteByAddressTypeId(int Id)
{
    Expression<Func<ClientAddressEntity, bool>> whereClause = ca => ca.AddressTypeId == Id;
    return deleteClientAddresses<DeleteByAddressTypeIdResponse>(whereClause);
}

public DeleteByClientIdResponse DeleteByClientId(int Id)
{
    Expression<Func<ClientAddressEntity, bool>> whereClause = ca => ca.ClientId == Id;
    return deleteClientAddresses<DeleteByClientIdResponse>(whereClause);
}

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Kevin Marois22-Apr-14 5:41

22-Apr-14 5:41

Great suggestions. Thanks.

So now I have another problem. I have:

public DeleteByAddressIdResponse DeleteAllClientAddressesByClientId(int Id)
{
    Expression<Func<ClientAddressEntity, bool>> whereClause = ca => ca.AddressId == Id;

    var response = deleteClientAddresses<DeleteByAddressIdResponse >(whereClause);

    return response;
}

and

private T deleteClientAddresses<T>(Expression<Func<ClientAddressEntity, bool>> whereClause) where T : _ResponseBase, new()
{
    var response = new T();

    using (var dc = getDataContext())
    {
        var entities = dc.ClientAddresses.Where(whereClause);   // <=================== Compilation error

        foreach (var entity in entities)
        {

        }
    }

    return response;
}

I'm getting

'System.Data.Linq.Table<Falcon.DAL.Data_Context.ClientAddress>' does not contain a definition for 'Where' and the best extension method overload

I installed system.linq.dynamic[^], which I thought would fix it, but no luck.

If it's not broken, fix it until it is

Richard Deeming22-Apr-14 6:32

Richard Deeming

22-Apr-14 6:32

Make sure you've got a reference to the System.Core assembly, and using System.Linq; at the top of your file.

Also, your types don't match. The error message reports a type of ClientAddress, whereas your predicate is looking at a type called ClientAddressEntity. You'll need to change the filters to be Expression<Func<Falcon.DAL.Data_Context.ClientAddress, bool>>.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Kevin Marois22-Apr-14 6:37

22-Apr-14 6:37

Same error.

And, even after adding a ref to System.Core, in the DAL code Core' is not found in System namespace.

This is bizarre.

If it's not broken, fix it until it is

Richard Deeming22-Apr-14 6:42

Richard Deeming

22-Apr-14 6:42

Sorry, I just spotted that your types don't match and updated my answer. Your filters are looking at the ClientAddressEntity type, whereas your table contains ClientAddress objects.

Your filters need to be:

Expression<Func<Falcon.DAL.Data_Context.ClientAddress, bool>>

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Kevin Marois22-Apr-14 6:44

22-Apr-14 6:44

That did it!

Many thanks

If it's not broken, fix it until it is

knapsack

Member 1076234920-Apr-14 9:52

Member 10762349

20-Apr-14 9:52

i have codin knap sack on c # and exe file

Pete O'Hanlon20-Apr-14 11:46

Pete O'Hanlon

20-Apr-14 11:46

Leave it round the back. I'll pick it up in the morning.

OriginalGriff20-Apr-14 20:49

OriginalGriff

20-Apr-14 20:49

Do not repost - it's rude, it wastes time, and it annoys people.

Those who fail to learn history are doomed to repeat it. --- George Santayana (December 16, 1863 – September 26, 1952)
Those who fail to clear history are doomed to explain it. --- OriginalGriff (February 24, 1959 – ∞)

Pete O'Hanlon20-Apr-14 21:57

Pete O'Hanlon

20-Apr-14 21:57

And it makes no sense.

OriginalGriff20-Apr-14 22:04

OriginalGriff

20-Apr-14 22:04

It's the haiku version! Laugh | :laugh:

ZurdoDev21-Apr-14 9:37

ZurdoDev

21-Apr-14 9:37

Do you have a question?

There are only 10 types of people in the world, those who understand binary and those who don't.

knap sack

Member 1076234920-Apr-14 9:43

Member 10762349

20-Apr-14 9:43

i have coding knap sack in c# and exe file

Pete O'Hanlon20-Apr-14 9:50

Pete O'Hanlon

20-Apr-14 9:50

But the elephant flies at midnight.
Yuri, is that you? Do you have the microfilm?

Manfred Rudolf Bihy20-Apr-14 10:22

Manfred Rudolf Bihy

20-Apr-14 10:22

The fora are dark
the fora are deep
but I have promises to keep
and miles to go before I sleep

Wink | ;)

"I had the right to remain silent, but I didn't have the ability!"

Ron White, Comedian

Wes Aday20-Apr-14 10:31

Wes Aday

20-Apr-14 10:31

Da.... the rooster barks at midnight. Look out for the elf.

I lost the microfilm comrade. I left it in some knapsack. OMG | :OMG:

OriginalGriff20-Apr-14 20:48

OriginalGriff

20-Apr-14 20:48

This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your HDD, or read your mind.
Edit your question and provide better information.

harold aptroot21-Apr-14 7:17

harold aptroot

21-Apr-14 7:17

If you're looking for the knapsack problem[^], there's pseudocode for a simple solution on wikipedia.